SQL Fundamentals: Understanding Query Execution Order
After learning how relational databases store data in structured tables with rows and columns linked via keys, the next interview trap is how SQL (Structured Query Language) actually processes a query. SQL execution order is a favourite interview trap - write order ≠ execution order. This matters because aliases, aggregation, filtering, joins, and LIMIT behave differently depending on the clause being evaluated.
- SQL execution order is a favourite interview trap - write order ≠ execution order.
- FROM + JOINs identify source tables, apply JOINs to create working dataset.
- WHERE filters rows BEFORE aggregation - cannot use aliases from SELECT here.
- GROUP BY collapses rows into groups by specified columns, while HAVING filters GROUPS after aggregation.
- SELECT chooses columns, applies aliases, and computes expressions, but aliases defined here are not available in WHERE/GROUP BY.
- ORDER BY sorts the final result set and CAN use SELECT aliases here.
- LIMIT / TOP returns only first N rows of sorted result.
SQL Write Order vs Execution Order
The big picture is simple: SQL clauses are written in one order, but logically processed in another. In interviews, the safest answer is to walk clause by clause, explain what happens, and name the common mistake attached to each stage.
SQL execution order is a favourite interview trap - write order ≠ execution order.
Why the Order Matters
FROM + JOINs run first because SQL must identify source tables and apply JOINs to create the working dataset. This is why forgetting JOIN type affects NULL handling before any filtering or aggregation begins.
WHERE filters rows BEFORE aggregation, so it cannot use aliases from SELECT. GROUP BY then collapses rows into groups by specified columns, and HAVING filters GROUPS after aggregation, where it can reference aggregate functions.
SELECT comes after these logical stages: it chooses columns, applies aliases, and computes expressions. ORDER BY then sorts the final result set and can use SELECT aliases, while LIMIT / TOP returns only first N rows of sorted result.
JOIN Types - Visual Reference
JOINs are part of the first execution stage, so their behaviour shapes the working dataset used by every later clause. The key interview nuance is that JOIN type affects NULL handling.
Filtering, Aggregation, and Aliases
The WHERE and HAVING distinction is one of the clearest ways to show execution-order understanding. WHERE filters rows BEFORE aggregation, while HAVING filters GROUPS after aggregation and can reference aggregate functions.
Alias logic follows the same principle. Aliases are applied in SELECT, so aliases defined here are not available in WHERE/GROUP BY, but ORDER BY can use SELECT aliases because it is evaluated later.
LIMIT / TOP and Final Results
ORDER BY sorts the final result set before LIMIT / TOP returns only first N rows of sorted result. The common mistake is applying LIMIT before ORDER BY in subqueries.
Structuring a SQL Fundamentals Interview Answer
"SQL execution order is a favourite interview trap - can you explain why write order is not the same as execution order, especially around aliases, aggregation, filtering, joins, and LIMIT?"
The #1 way candidates get this wrong is using a SELECT alias in WHERE. Aliases are defined in SELECT, so they are not available in WHERE/GROUP BY, but ORDER BY can use SELECT aliases.
Using a SELECT alias in WHERE is the most frequent execution-order error. It costs points because WHERE filters rows BEFORE aggregation and before SELECT applies aliases, so the alias does not exist at that stage.
Conclusion
SQL execution order is the key to explaining aliases, aggregation, filtering, joins, and LIMIT correctly in interviews. Remember the logical sequence: FROM + JOINs, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT / TOP.