What is the difference between the WHERE clause and the HAVING clause in SQL?

Question

Aryan Kumar · Answer

The WHERE and HAVING clauses in SQL are both used to filter the results of a SELECT statement. However, they work in different ways.

The WHERE clause is used to filter the rows of data before any grouping is done. It is used to filter individual rows, based on certain conditions. For example, the following WHERE clause will only return rows where the customer_id is equal to 100:

SQL

SELECT * FROM customers
WHERE customer_id = 100;

The HAVING clause is used to filter the groups of data after grouping has been done. It is used to filter groups of rows, based on conditions involving aggregate functions. For example, the following HAVING clause will only return groups where the average order amount is greater than $100:

SQL

SELECT customer_id, AVG(total_amount) AS average_order_amount
FROM orders
GROUP BY customer_id
HAVING AVG(total_amount) > 100;

In other words, the WHERE clause filters the rows before they are grouped, and the HAVING clause filters the groups after they are grouped.

Here is a table that summarizes the key differences between the WHERE and HAVING clauses:

Clause	Description
WHERE	Filters the rows of data before any grouping is done.
HAVING	Filters the groups of data after grouping has been done.
Used to filter individual rows	Used to filter groups of rows
Can be used with any SELECT statement	Can only be used with SELECT statements that have a GROUP BY clause

forum

What is the difference between the WHERE clause and the HAVING clause in SQL?

Can you answer this question?

1 Answers