What is the difference between WHERE and HAVING?
Both filter rows, but at different stages of query execution.
WHERE — filters rows before grouping and aggregation. Cannot reference aggregate functions like
SUM()orCOUNT().
HAVING — filters groups after
GROUP BYhas been applied. Can reference aggregate results.
Order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
-- WHERE: filter rows before grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active' -- Filter ROWS (before grouping)
GROUP BY department;
-- HAVING: filter groups after aggregation
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 50000; -- Filter GROUPS (after aggregation)
-- Both together
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > '2020-01-01' -- Filter: only recent hires
GROUP BY department
HAVING COUNT(*) >= 5 -- Filter: only departments with 5+ people
ORDER BY avg_sal DESC
LIMIT 10;
-- ERROR: cannot use aggregate in WHERE
-- SELECT department FROM employees WHERE COUNT(*) > 5; -- ERROR!WHERE filters individual rows (status = 'active') before grouping. HAVING filters groups (AVG(salary) > 50000) after aggregation.
Both can be used together: WHERE narrows the data, GROUP BY aggregates, HAVING filters the aggregated results. Using an aggregate in WHERE is a syntax error — must use HAVING.
WHERE = before GROUP BY (rows), HAVING = after GROUP BY (groups). WHERE cannot use aggregates. HAVING can.
This is the #1 most asked SQL filtering question. Know the execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.