What Is the Difference Between HAVING and WHERE Clause in SQL?

The WHERE and HAVING clause differ in the following ways:

1. Order of Execution: WHERE and HAVING have a different order of execution in a SQL query. Conditions in WHERE are applied before groupings and aggregations are applied, while the conditions in HAVING are applied after;
2. Application: WHERE is applied to individual rows, while HAVING is applied to grouped records;
3. Use of Aggregation Functions: In HAVING, use of aggregation functions to filter data is allowed, while in WHERE use of these are not allowed;
4. Refactoring: HAVING is meant as a convenience and is not necessary; it can be refactored to use WHERE instead.

Order of Execution

Typically, the steps of a SQL query execution in the context of WHERE and HAVING are as follows:

1. Conditions in the WHERE clause are applied to limit the number of rows;
2. If the query contains GROUP BY and/or aggregation functions, resulting rows from WHERE are grouped and aggregated accordingly;
3. Conditions in the HAVING clause are applied after aggregation takes place so the aggregated data can be filtered.

Application

Essentially, both, HAVING and WHERE are conditions and look similar. However, WHERE is meant to filter rows of data read from tables before grouping/aggregation takes place, while HAVING is meant to filter the aggregated data (which is not known in the initial stages of a query).

Use of Aggregation Functions

In HAVING we can use aggregation functions to filter data, while in WHERE use of these are not allowed. This is because at the time WHERE is executed, the result of aggregation functions is not known. However, since, HAVING is executed after the grouping and aggregation of data has taken place, the use of aggregation functions is allowed in HAVING. For example:

SELECT dept_id, SUM(sales_amount)
FROM sales
WHERE date_sales = '01-Jan-2000';
GROUP BY dept_id
HAVING SUM(sales_amount) > 1000

If a query does not have a GROUP BY clause, the rows returned by the WHERE clause are seen as a "single group" and the HAVING clause can be used to further filter the results (for example, by using aggregation functions). For example:

SELECT COUNT(*)
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id
WHERE dept.name IN ('sales', 'marketing')
HAVING COUNT(*) > 10;

Although, this kind of query is possible, it might not be super useful on its own in most cases.

Refactoring

The use of HAVING clause is meant as a convenience and is not necessary. Queries with HAVING can be refactored to use WHERE instead as demonstrated in the following example:

SELECT dept.name, COUNT(*)
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id
GROUP BY dept.name
HAVING COUNT(*) > 1;

The above query can be refactored to remove HAVING and use WHERE in the following way:

SELECT * FROM (
   SELECT dept.name AS deptName, COUNT(*) AS empCount
   FROM emp
   JOIN dept ON emp.dept_id = dept.id
   GROUP BY dept.name
) AS result
WHERE result.empCount > 1;

Even though this can be done, HAVING makes the query much more readable and shorter in size.