How can HAVING and WHERE behave differently in queries involving JOINs?

Difficulty: 5/10

JOIN filtering, HAVING vs WHERE, SQL aggregation

Difference Between WHERE and HAVING in JOIN Queries

In JOIN queries, the WHERE and HAVING clauses behave differently because they filter data at different stages of query execution. WHERE filters rows before grouping and aggregation, while HAVING filters rows after grouping. This distinction becomes especially important when JOINs produce multiple rows or NULLs (in LEFT JOINs).

Example: Count orders per customer — but filter differently using WHERE vs HAVING

Key Differences

1
WHERE filters rows before JOIN results are aggregated.
2
WHERE can remove rows—including NULLs—from a LEFT JOIN, effectively turning it into an INNER JOIN if you filter columns from the right table.
3
HAVING filters after GROUP BY, using aggregate values such as COUNT(), SUM(), AVG().
4
HAVING can be used to filter groups even when WHERE cannot (e.g., filtering based on COUNT(o.order_id)).

In summary, use WHERE to filter raw rows before grouping, and use HAVING to filter aggregated results after GROUP BY.

Scenario Questions

0-2 years experience

2-5 years experience

Follow-up Questions