Detailed explanation
The logical processing order of a SELECT query is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This describes how SQL defines the query’s result, not necessarily the physical sequence used by the database engine. Although SELECT appears first in the written query, SQL must first establish the source rows before deciding which values to return. Interviewers expect you to explain this distinction and connect the order to filtering, aggregation, and alias visibility.
FROM identifies the source tables, while JOIN combines their rows according to join conditions and join type. WHERE then filters individual rows; only conditions evaluating to TRUE retain a row. GROUP BY arranges the remaining rows into groups so aggregate functions such as COUNT, SUM, and AVG can produce group-level values. HAVING filters these groups after aggregation. Without GROUP BY, an aggregate query can treat its input as a single group. The important distinction is that WHERE filters rows before grouping, whereas HAVING filters groups based on grouping columns or aggregate results.
SELECT determines the output expressions and their aliases. DISTINCT, when present, removes duplicate output rows. ORDER BY sorts the result, and LIMIT restricts how many rows are returned; other SQL dialects use syntax such as FETCH FIRST or TOP. Because WHERE is logically earlier than SELECT, a SELECT alias generally cannot be referenced in WHERE. ORDER BY can normally use that alias because it is logically later. Alias support in GROUP BY and HAVING varies across databases, so avoid presenting those rules as universal.
In the example, SQL reads employees, keeps rows where active = 1, groups them by dept_id, and retains departments with COUNT(*) > 2. It then returns dept_id and the count aliased as c, sorts by c descending, and returns at most five departments. If counts tie, adding dept_id to ORDER BY makes the selection deterministic. Under the hood, the optimiser may push down filters, reorder eligible joins, or use indexes while preserving the query’s meaning. Explain the logical stages without claiming that the engine literally executes each clause in that sequence.
Key points to remember
- Logical order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
- WHERE filters individual rows; HAVING filters groups after aggregation.
- SELECT aliases are generally unavailable in WHERE but available in ORDER BY.
- DISTINCT removes duplicate projected rows before ordering and limiting.
- Logical processing defines meaning; the optimiser chooses the physical execution plan.
Common mistakes candidates make
- Reciting the written clause order or claiming that logical order is the physical execution sequence.
- Using an aggregate condition such as COUNT(*) > 2 in WHERE instead of HAVING.
- Assuming LIMIT alone returns a predictable set of rows, or ignoring ties in ORDER BY.
Likely follow-up questions
Why can COUNT(*) > 2 not be placed in WHERE in this example?
WHERE filters individual employee rows before department groups and their counts are established. HAVING applies the condition to each department’s aggregate count.
How can I filter using a calculated SELECT alias?
Define the calculated column in a subquery or CTE, then reference its alias in the outer query’s WHERE clause. At that outer level, the alias is an input column rather than an alias being created by the same SELECT.
Why can moving a condition from ON to WHERE change a LEFT JOIN result?
ON determines matches before the LEFT JOIN supplies NULL values for unmatched right-side rows. A WHERE condition such as right_table.status = 'active' rejects those NULL-extended rows, whereas placing it in ON preserves unmatched left-side rows.
Interview tip: State the sequence first, then walk through the example and use alias visibility to demonstrate why the order matters.