What is the order of SQL clause execution?
SQLLevel: FoundationFundamentals (Easy)
What is the order of SQL clause execution?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
SQL doesn't execute in the order you write it. The logical execution order is:
FROM(including JOINs)WHEREGROUP BYHAVINGSELECTDISTINCTORDER BYLIMIT/FETCH
This is why you can't use a column alias defined in SELECT inside a WHERE clause — WHERE runs before SELECT.
-- This FAILS in most databases
SELECT salary * 1.1 AS adjusted_salary
FROM employees
WHERE adjusted_salary > 50000; -- adjusted_salary doesn't exist yet
-- This works
SELECT salary * 1.1 AS adjusted_salary
FROM employees
WHERE salary * 1.1 > 50000;
-- Or use a subquery/CTE
WITH base AS (
SELECT salary * 1.1 AS adjusted_salary FROM employees
)
SELECT * FROM base WHERE adjusted_salary > 50000;
Common wrong answer: Writing the clauses in order and assuming that's the execution order.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT