What is the difference between HAVING and WHERE clause?

What is the difference between HAVING and WHERE clause is one the most popular question asked in interviews especially to the freshers? Though these two clauses are very similar and they primarily restrict the tuples returned by the SELECT statement, the main difference arises when used with the GROUP BY clause. So, lets first learn about these two clauses and then their differences.

WHERE Clause

The WHERE clause is used to fetch the data which specify the given condition. It is used to filter records and select only necessary records. It is used with SELECT, UPDATE, DELETE, etc. query. The SQL also implements and, or, and not in the WHERE clause which is known as the boolean condition.

Example: Take an example of a table Transaction that has ‘Item’, ' Month' and ‘Amount ’ as attributes.

If we want to calculate the total sale by two products TV and Fridge then our Query statement would be:

SELECT Item, sum(Amount) AS Net_amount

FROM Transaction

WHERE Item in ( ‘TV’, ‘Fridge’)

GROUP BY Item;

The output of the above query will be:

HAVING Clause

The HAVING clause is generally used along with the GROUP BY clause. This clause is used in the column operation and is applied to aggregate rows or groups according to given conditions.

Example: Consider the above example. Now, if we want to calculate the Net_amount of the total sale by two products TV and Fridge then our query statement would be:

SELECT Item, sum(Amount) AS Net_amount

FROM Transaction

GROUP BY Item

HAVING Item in (‘TV’, ‘Fridge’);

The output of the above query will be:

Now, if we want only those items which have the net_amount greater than 1200. The query can be written as:

SELECT Item, sum(Amount) AS Net_amount

FROM Transaction

GROUP BY Item

HAVING sum(Net_amount)>1200;

The output of the above query will be:

This cannot be done in the WHERE clause. It generates an error message when we use the WHERE clause with the aggregate functions.

Difference between WHERE and HAVING clause

The WHERE clause is used in the selection of rows according to given conditions whereas the HAVING clause is used in column operations and is applied to aggregated rows or groups.
If GROUP BY is used then it is executed after the WHERE clause is executed in the query. It means it selects the rows before grouping is done or aggregate calculations are performed. Thatswhy, WHERE clause is also called Pre-filter . But, GROUP BY is executed before the execution of the HAVING clause. It means it selects the rows after aggregate calculations are performed. Thatswhy, HAVING clause is also called as Post-filter .
We cannot use the HAVING clause without SELECT statement whereas the WHERE clause can be used with SELECT, UPDATE, DELETE, etc.
WE can use aggregate functions like sum, min, max, avg, etc with the HAVING clause but they can never be used with WHERE clause .
HAVING clause is generally used with the GROUP BY. If you use the HAVING clause without GROUP BY then also it can refer to any column but it won't be used while performing the query unlike WHERE clause.

Example: The following query has the same results. The WHERE clause uses the “age” index but the having clause will scan the full table instead of directly looking at the "age" column.