SQL is written in a different order from its logical processing order. Starting with FROM, the database takes a table, filters rows, groups them, computes expressions, and sorts the result. Follow the table through each step.
A simulator of logical SQL processing and its intermediate tables
1. Change the conditions
2. Advance through the processing steps
The two rows above show written order and logical processing order. The same clause is highlighted in both so you can compare its position.
WHERE and HAVING filter different things
WHERE filters individual rows before grouping; HAVING filters groups afterward. Aggregate functions such as COUNT(*) and AVG() therefore belong in HAVING, not directly in WHERE. Put row-level conditions in WHERE when possible to reduce the input to grouping.
Aliases become available at SELECT
The aliases average and headcount are introduced at step 5, SELECT. Earlier steps cannot generally rely on them, while the later ORDER BY can. Some databases, including MySQL, also allow aliases in GROUP BY and HAVING; check the rules for your database.
Coding exercises and interviews
Departments with an average salary of at least 3,500 calls for GROUP BY + HAVING. Department averages for employees earning at least 3,000 calls for WHERE + GROUP BY. Identify whether each condition applies to individual rows or aggregates. In this model, SELECT uses grouped columns and aggregate expressions.