Why Execution Order Matters
Understanding how SQL processes queries is crucial for:
- Writing correct queries
- Understanding why certain errors occur
- Optimizing query performance
- Knowing what aliases are available at each stage
💡 SQL is declarative (you describe WHAT), but the database processes it in a specific order (HOW).
The Logical Execution Order
1. FROM → Identify tables and JOINs
2. WHERE → Filter rows
3. GROUP BY → Group remaining rows
4. HAVING → Filter groups
5. SELECT → Compute columns and expressions
6. DISTINCT → Remove duplicate rows
7. ORDER BY → Sort the result
8. LIMIT/OFFSET → Restrict number of rows returned
Detailed Breakdown
1. FROM / JOIN
The database first identifies which tables to use and performs all JOINs.
SELECT * FROM orders
-- FROM is processed first
2. WHERE
Filters individual rows before any grouping.
SELECT * FROM orders
WHERE amount > 100
-- Only rows with amount > 100 survive
3. GROUP BY
Groups the remaining rows by specified columns.
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
-- Rows grouped by department
4. HAVING
Filters groups after aggregation.
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
-- Only groups with 5+ employees remain
5. SELECT
Computes the final column values and expressions.
SELECT
department,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
-- SELECT runs after FROM, WHERE, GROUP BY, HAVING
6. DISTINCT
Removes duplicate rows from the result.
SELECT DISTINCT department
FROM employees
WHERE salary > 50000
-- DISTINCT runs after SELECT
7. ORDER BY
Sorts the final result set.
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC
-- ORDER BY can reference SELECT aliases
8. LIMIT / OFFSET
Restricts the number of rows returned.
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC
LIMIT 10
-- LIMIT runs last
Practical Examples
Why Column Aliases Work in ORDER BY
SELECT
first_name,
last_name,
salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC; -- Works! Alias created in step 5, ORDER BY is step 7
Why Column Aliases Don't Work in WHERE
-- WRONG: This fails
SELECT
first_name,
salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000; -- Error! annual_salary doesn't exist yet
-- CORRECT: Use the expression
SELECT
first_name,
salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;
Why WHERE Can't Use Aggregate Results
-- WRONG: This fails
SELECT department, COUNT(*) AS count
FROM employees
WHERE count > 5 -- Error! WHERE runs before aggregation
GROUP BY department;
-- CORRECT: Use HAVING
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Complete Query Example
-- Find the top 3 departments by average salary
-- for active employees hired after 2022
SELECT
department,
COUNT(*) AS employee_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees -- 1. FROM
WHERE is_active = TRUE -- 2. WHERE
AND hire_date > '2022-01-01' -- 2. WHERE
GROUP BY department -- 3. GROUP BY
HAVING COUNT(*) >= 3 -- 4. HAVING
ORDER BY avg_salary DESC -- 7. ORDER BY
LIMIT 3; -- 8. LIMIT
✏️ Exercise: Explain why this query fails and fix it:
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE avg_sal > 75000
GROUP BY department;
See Solution
The query fails because WHERE runs before SELECT, so avg_sal doesn't exist yet. Use HAVING instead:
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;
✅ Key Takeaways
- SQL executes in order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
- WHERE cannot use column aliases or aggregate functions
- HAVING can use aggregate functions and is evaluated after GROUP BY
- ORDER BY can reference aliases created in SELECT
- Understanding execution order prevents common SQL errors