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.
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(*)?