WHERE clause vs. HAVING clause

WHERE clause

The WHERE clause in SQL is used while fetching data from a table. Any row that does not match the condition given in the WHERE clause is not fetched. The clause works on row’s data and not on aggregated data; hence, the WHERE clause is applied to filter data before grouping the data (using GROUP BY) for further processing. However, it is not always necessary to apply the GROUP BY clause after the WHERE clause as the WHERE clause can also be applied alone.

HAVING clause

The HAVING clause in SQL is also used while fetching data from a table. The clause works on aggregated(grouped) data and is evaluated after the grouping is applied to the data. Hence, the HAVING clause is used to filter grouped data after applying the WHERE clause. Unlike the WHERE clause, which can be applied without GROUP BY, the HAVING clause requires the GROUP BY clause before it can be applied to the data. Since the data is grouped, the HAVING clause can contain aggregate functions such as COUNT(), MAX(), and MIN().

SQL statements often have both the WHERE and the HAVING clause. The WHERE clause is applied first to filter each row based on a specific condition. The speed of computation is faster since the operations are applied to a much smaller, filtered dataset. The HAVING clause is used to filter the grouped data according to the condition. The performance is considerably less in the HAVING clause since the filtering is applied to the grouped dataneeds to be sorted first, which adds computational cost.