1. 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.
2. 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. Thats why, 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.
3. We cannot use the HAVING clause without SELECT statement whereas the WHERE clause can be used with SELECT, UPDATE, DELETE, etc.
4. We can use aggregate functions like sum, min, max, avg, etc with the HAVING clause but they can never be used with WHERE clause.
5. 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.
1. WHERE clause
SELECT * FROM Table WHERE age = 10
2. HAVING clause
SELECT * FROM Table HAVING age = 10