GROUP BY & HAVING
SQL · 6 interview questions
GROUP BY collapses rows that share a value into one row per distinct value, and aggregates describe each bucket. Once you've grouped, the individual rows are gone — you can only ask questions about the groups.
That's the whole explanation for WHERE versus HAVING. WHERE runs before grouping, so it filters individual rows and cannot see aggregates. HAVING runs after, so it filters whole groups and is the only place an aggregate condition can go. "Classes with more than five students" is a HAVING; "students enrolled after 2020" is a WHERE. A question that needs both gets both, and that's the version interviewers prefer to ask.
Putting a condition in the right one isn't just about correctness, it's about work. Filtering rows in WHERE means fewer rows to group; filtering in HAVING means the database grouped them and then threw the groups away. When both would be correct, WHERE is the cheaper place.
GROUP BY also has a NULL quirk worth knowing: all NULLs in the grouped column collapse into a single group together, even though NULL doesn't equal NULL anywhere else in the language.
GROUP BY & HAVING interview questions
- What's the difference between WHERE and HAVING?
- WHERE filters individual rows before grouping and cannot reference aggregates. HAVING filters groups after aggregation and is the only place an aggregate condition may appear. A query can use both, and should when it needs both.
- Why they ask: Asked in essentially every SQL interview. The 'before/after grouping' framing is the answer that lands.
- If a condition would work in either WHERE or HAVING, which should you use?
- WHERE. It removes rows before the grouping work happens, so the database aggregates less. HAVING would build the groups and then discard them. Same answer, more work.
- Why they ask: The follow-up that separates 'knows the rule' from 'knows why the rule exists'.
- How does GROUP BY handle NULL?
- All NULLs collapse into one group. This is a deliberate exception — everywhere else in SQL, NULL doesn't equal NULL, but grouping treats them as a single bucket.
- Why they ask: Surprising given the rest of SQL's NULL rules, which is exactly why it gets asked.
- Can you use HAVING without GROUP BY?
- Yes. Without GROUP BY the whole table is one group, so
SELECT COUNT(*) FROM t HAVING COUNT(*) > 10returns one row or none. It's legal, occasionally useful, and almost always clearer written another way. - Why they ask: A good sanity check on whether the 'HAVING filters groups' model is actually understood.
- When should you use GROUP BY instead of DISTINCT?
- For deduplication alone they're equivalent and DISTINCT reads better. Use GROUP BY the moment you need an aggregate per bucket, or a HAVING filter — DISTINCT can't do either.
- Why they ask: People reach for DISTINCT when they need per-group counts, then get stuck.
- How do you find groups that contain every one of a set of values?
- Group by the entity, then require the count of distinct matching values to equal the size of the set:
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product). DISTINCT matters — duplicate rows would otherwise inflate the count past the threshold. - Why they ask: The 'customers who bought all products' pattern, which is a relational division in disguise.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.