WHERE and HAVING both restrict which data appears in a query's result, but they act at different points in query execution -- WHERE filters individual rows before any grouping or aggregation occurs, while HAVING filters entire groups after GROUP BY has aggregated the rows.
Key Points: • WHERE operates on raw, non-aggregated columns and cannot reference aggregate functions. • HAVING operates on the output of GROUP BY and can filter using aggregate functions like SUM() or AVG(). • Filtering early with WHERE is more efficient, since fewer rows need to be aggregated. • Both clauses can be combined in the same query: WHERE narrows the rows first, then HAVING narrows the resulting groups. • Without a GROUP BY, HAVING can still technically be used, treating the whole result as one group, but this is uncommon in practice.
Example: To find departments whose total salary spend exceeds $500,000, you'd group employees by department and filter with HAVING SUM(salary) > 500000, since that condition depends on an aggregate that only exists after grouping.
Code Example:
SELECT department_id, SUM(salary) AS total_salary
FROM employees
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING SUM(salary) > 500000;Interview Tip: A concise interview answer is:
"WHERE filters rows before grouping and works on raw column values, while HAVING filters groups after GROUP BY and can use aggregate functions like SUM or COUNT. I use WHERE to cut down the data early for performance, then HAVING for conditions that depend on an aggregated result, and they're often combined in the same query."