Detailed explanation
WHERE and HAVING both filter query results, but they operate at different logical stages. WHERE filters individual input rows before grouping or aggregation. HAVING filters the groups remaining after aggregation, typically using conditions on aggregate values such as COUNT(*), SUM(salary), or AVG(salary). This distinction matters because filtering rows changes what goes into an aggregate, while filtering groups decides which calculated results are returned. Interviewers expect you to explain this effect, not just say that HAVING is used with GROUP BY.
Consider: SELECT dept_id, COUNT(*) AS total FROM employees WHERE active = 1 GROUP BY dept_id HAVING COUNT(*) > 5; First, WHERE retains only active employees. GROUP BY then collects those rows by department, and COUNT(*) calculates the number of active employees in each department. Finally, HAVING keeps departments with more than five active employees. A department containing twenty employees but only four active employees is excluded. Without the WHERE condition, the count would include inactive employees too, changing the meaning of the query.
The relevant logical processing order is FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY. An aggregate calculated at the current query level cannot appear directly in WHERE because its input has not yet been grouped or aggregated at that stage. However, WHERE can compare a row value against an aggregate returned by a subquery, such as salary > (SELECT AVG(salary) FROM employees). HAVING does not always require an explicit GROUP BY: an aggregate query can treat all qualifying rows as one group. The database optimiser may choose a different physical execution plan while preserving these logical semantics.
Use WHERE for conditions that define eligible input rows and HAVING for conditions that define eligible aggregate results. Filtering rows early can reduce aggregation work, although actual performance depends on indexes, data distribution, and the execution plan. HAVING can also test grouping columns, but equivalent conditions on those columns are generally clearer in WHERE. For portable SQL, write HAVING COUNT(*) > 5 rather than relying on the SELECT alias total, because alias visibility varies across database systems.
Key points to remember
- WHERE filters individual rows before grouping and aggregation.
- HAVING filters groups after aggregate values have been calculated.
- Current-query aggregates cannot be used directly in WHERE, but aggregates inside subqueries can.
- HAVING can work without explicit GROUP BY by filtering a single aggregate group.
- Choose the clause based on the intended result, not a blanket performance rule.
Common mistakes candidates make
- Writing WHERE COUNT(*) > 5 instead of placing the aggregate condition in HAVING.
- Assuming HAVING always requires GROUP BY or must always contain an aggregate function.
- Moving conditions between WHERE and HAVING without checking whether the aggregate inputs and results change.
Likely follow-up questions
Can HAVING be used without GROUP BY?
Yes. For example, SELECT COUNT(*) AS total FROM employees HAVING COUNT(*) > 5 treats all employees as one aggregate group. It returns one row if the count exceeds five, otherwise no rows.
Can WHERE and HAVING be used together?
Yes: WHERE defines which rows participate in aggregation, and HAVING decides which resulting groups are returned. In the example, WHERE selects active employees, while HAVING selects departments with more than five of them.
Is WHERE faster than HAVING?
There is no universal speed comparison because the clauses usually perform different jobs. An equivalent filter on a grouping column is generally best written in WHERE to reduce aggregation input, but an optimiser may push a suitable HAVING condition down automatically.
Interview tip: Explain row filtering versus group filtering first, then walk through the active-employees example to demonstrate how each clause changes the result.