← SQL roadmap

Topic 42 of 52 · Beginner

When should a filter use WHERE instead of HAVING?

WHERE filters input rows before grouping; HAVING filters groups after aggregate values have been calculated. They answer different questions and are not interchangeable.

BasicEntry level · 0–1 yearsAggregation

MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.

Detailed answer

WHERE filters input rows before grouping; HAVING filters groups after aggregate values have been calculated. They answer different questions and are not interchangeable.

To report cities with revenue above a threshold from delivered orders, filter status with WHERE, group by city, then filter SUM(amount) with HAVING.

Filtering early reduces rows entering aggregates. An optimizer may push some safe predicates down, but moving an aggregate-dependent condition into WHERE is invalid.

Internal working

Logical evaluation starts with FROM and joins, then WHERE, grouping and HAVING; physical execution can reorder operations while preserving the result.

Practical scenario

Cancelled orders must not contribute to a city revenue threshold; removing cities after aggregating all orders produces the wrong business metric.

SELECT city, SUM(amount) AS revenue FROM orders
WHERE status = 'delivered' GROUP BY city HAVING SUM(amount) > 1000;
Common mistakes
  • Using SUM in WHERE.
  • Selecting an arbitrary non-grouped city attribute.
Key takeaways
  • Filter rows before groups.
  • State the aggregation grain.
  • Do not rely on physical evaluation order.
Follow-up challenges
  • Can HAVING be used without GROUP BY?
  • How does a LEFT JOIN affect COUNT(*)?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.