HAVING vs WHERE
Overview
This is the most frequently asked SQL interview question. WHERE filters raw rows before they are grouped. But what if you want to filter the groups themselves? For example, 'Show me departments where the Total Payroll is greater than $1,000,000'. You cannot put SUM(payroll) > 1M in a WHERE clause, because the WHERE clause runs before the SUM even exists! The HAVING clause was invented exclusively to filter aggregated data after the GROUP BY has finished.
Syntax
-- The Execution Order: WHERE -> GROUP BY -> HAVING
SELECT department, SUM(salary) AS total_payroll
FROM employees
-- 1. Filter raw rows FIRST (Only look at Full-Time employees)
WHERE employment_type = 'Full-Time'
-- 2. Group the surviving rows into buckets
GROUP BY department
-- 3. Filter the BUCKETS! (Only show massive departments)
HAVING SUM(salary) > 1000000;Common Pitfalls
- Using
HAVINGto filter standard columns (e.g.,HAVING status = 'Active'). While this technically works in some databases, it is catastrophically slow. If you useHAVING, the database must group all the inactive rows first, do the heavy math, and then throw them away. If you useWHERE, it immediately discards the inactive rows, saving massive CPU cycles. - Trying to use the Alias in the
HAVINGclause. (e.g.,HAVING total_payroll > 1M). BecauseHAVINGruns beforeSELECT, the aliastotal_payrollhasn't been created yet. You must repeat the explicit function:HAVING SUM(salary) > 1M.
Interview Questions
HAVING clause without a GROUP BY clause?Technically yes. If you omit GROUP BY, the entire table is treated as one massive, single group. A query like SELECT SUM(salary) FROM emp HAVING SUM(salary) > 100 is valid, though rarely useful in production.
Real-World Example
Finding spam accounts by identifying users who have posted an abnormally high number of comments in a short time.
SELECT user_id, COUNT(*) AS comment_count
FROM comments
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY user_id
-- Only flag the user if they posted more than 100 comments today
HAVING COUNT(*) > 100;Check Your Knowledge
Test your understanding of HAVING vs WHERE with these quick questions.