SQL Execution Order: 9 Steps Every Developer Must Know

1. Introduction

One of the most common sources of confusion for SQL learners — and a frequent source of bugs even for experienced developers — is the difference between how you write a SQL query and how the database engine actually executes it.

You write SQL in a human-readable order: SELECT at the top, FROM below it, then WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. But the database engine does not execute clauses in that order. It processes them in a completely different sequence — a logical order determined by the relational algebra the query is built on.

Understanding the execution order explains why you cannot reference a SELECT alias in a WHERE clause, why aggregate functions are unavailable in WHERE but allowed in HAVING, why DISTINCT runs after SELECT, and many other behaviours that seem mysterious at first glance. This blog walks through every step of the logical execution order with examples, output tables, and practical implications.

2. The Nine Steps — Logical Execution Order

The diagram below shows all nine steps in the logical order of execution, the role of each clause, and a side-by-side comparison of the writing order versus the execution order.

Figure 1: SQL Query — Logical Order of Execution (9 steps with alias visibility & writing order comparison)

Quick Summary

3. Step 1 — FROM: Building the Initial Dataset

The FROM clause is the very first thing the database processes. It identifies every table or subquery involved in the query and creates an initial working dataset. When multiple tables are listed with commas (old-style join), the engine produces their Cartesian product — every row from table A paired with every row from table B.

In practice, the optimizer never literally materialises the full Cartesian product for a JOIN — it uses index lookups, hash joins, or merge joins. But logically, FROM defines the universe of rows that all subsequent steps work with.

-- FROM is processed first
SELECT *
FROM   employees e          -- Step 1a: load employees
JOIN   departments d         -- Step 1b: Cartesian product with departments
  ON   e.dept_id = d.id;    -- Step 2: apply JOIN condition to filter

Subqueries and CTEs in the FROM clause are also materialised at this step — their inner query is run first, then treated as a virtual table.

4. Step 2 — JOIN / ON: Applying Join Conditions

After FROM builds the initial row set, JOIN conditions (ON clause) are applied to combine rows from different tables. This step determines which rows from each table survive into the working dataset for subsequent steps.

Different join types produce different results at this step:

-- Sample tables for the examples throughout this blog
-- employees: emp_id, name, dept_id, salary, hire_year
-- departments: id, dept_name, location, budget
SELECT e.name, d.dept_name, e.salary
FROM   employees  e
JOIN   departments d ON e.dept_id = d.id   -- ON runs at Step 2
WHERE  e.salary > 70000;                    -- WHERE runs at Step 3

OUTPUT: INNER JOIN result — only matching rows from both tables

5. Step 3 — WHERE: Filtering Individual Rows

WHERE is applied after FROM and JOIN but before GROUP BY. It filters individual rows from the combined dataset. Because GROUP BY has not yet run, aggregate functions (SUM, AVG, COUNT, etc.) are not allowed in WHERE — the groups do not exist yet.

Because SELECT has also not yet run, column aliases defined in SELECT are not visible in WHERE. This is one of the most common mistakes SQL beginners make.

-- CORRECT: filter on actual column, not alias
SELECT name, salary, salary * 1.1 AS projected_salary
FROM   employees
WHERE  salary > 70000;     -- 'salary' exists; 'projected_salary' does NOT yet
-- WRONG: will raise an error — alias not visible in WHERE
-- WHERE  projected_salary > 77000;   -- ERROR: column not found
-- WORKAROUND: use a subquery or CTE
SELECT * FROM (
    SELECT name, salary, salary * 1.1 AS projected_salary FROM employees
) AS sub
WHERE projected_salary > 77000;

OUTPUT: WHERE salary > 70000 — filters BEFORE grouping

Never use aggregate functions in WHERE. They belong in HAVING. Example: WHERE AVG(salary) > 80000 is invalid — use HAVING AVG(salary) > 80000 instead.

6. Step 4 — GROUP BY: Collapsing Rows into Groups

After WHERE has filtered individual rows, GROUP BY collapses those rows into groups. Each unique combination of the GROUP BY columns becomes one row in the working dataset. This is when aggregates are computed — the multiple input rows per group become a single summary row.

Once GROUP BY runs, the working dataset only has one row per group. All subsequent clauses (HAVING, SELECT, ORDER BY) operate on these grouped rows, not on original individual rows.

SELECT   department,
         COUNT(*)     AS headcount,
         AVG(salary)  AS avg_salary,
         MAX(salary)  AS max_salary,
         MIN(salary)  AS min_salary
FROM     employees
WHERE    status = 'Active'          -- Step 3: filter rows first
GROUP BY department                  -- Step 4: collapse to one row per dept
ORDER BY avg_salary DESC;            -- Step 8: sort the grouped result

OUTPUT: GROUP BY department — one summary row per department

Non-aggregated columns in SELECT must appear in GROUP BY. Otherwise the database doesn’t know which row’s value to show for each group.

7. Step 5 — HAVING: Filtering Groups

HAVING filters the groups produced by GROUP BY — not individual rows. Because HAVING runs after GROUP BY and after aggregates are computed, aggregate functions are allowed here. This is the key difference from WHERE.

SELECT   department,
         COUNT(*)    AS headcount,
         AVG(salary) AS avg_salary
FROM     employees
WHERE    status = 'Active'             -- Step 3: filter individual rows
GROUP BY department                     -- Step 4: group
HAVING   AVG(salary) > 65000            -- Step 5: filter groups by aggregate
ORDER BY avg_salary DESC;

OUTPUT: HAVING AVG(salary) > 65000 — only departments meeting the threshold

HR (avg=59500) is excluded by HAVING. It survived WHERE because its individual employees are Active, but the group-level average fails the threshold.

8. Step 6 — SELECT: Evaluating Expressions & Defining Aliases

SELECT runs sixth — after the data has been sourced, joined, filtered, grouped, and having-filtered. This is when column expressions are evaluated, aliases are defined, and window functions are computed. From this step onward, SELECT aliases are visible.

This explains why aliases defined in SELECT cannot be used in WHERE, GROUP BY, or HAVING — those clauses run before SELECT. They CAN be used in ORDER BY and LIMIT, which run after SELECT.

SELECT
    name,
    salary,
    salary * 1.15                             AS with_bonus,    -- expression
    CASE WHEN salary > 80000 THEN 'High'
         ELSE 'Standard' END                  AS pay_band,      -- CASE
    RANK() OVER (ORDER BY salary DESC)        AS salary_rank,   -- window function
    COUNT(*) OVER ()                          AS total_employees -- window agg
FROM employees
WHERE status = 'Active'
ORDER BY salary_rank;     -- ORDER BY (Step 8) can use SELECT aliases

OUTPUT: SELECT evaluates all expressions and window functions at Step 6

9. Step 7 — DISTINCT: Removing Duplicate Rows

DISTINCT is applied after SELECT evaluates all expressions. It removes duplicate rows from the SELECT output. Because it runs after SELECT, it compares the final computed values, not the raw column values.

-- Find unique department and pay_band combinations
SELECT DISTINCT
    department,
    CASE WHEN salary > 80000 THEN 'High'
         ELSE 'Standard' END AS pay_band
FROM employees
ORDER BY department;

OUTPUT: DISTINCT runs after SELECT evaluates the CASE expression

Without DISTINCT, Engineering would appear three times (once per employee). DISTINCT deduplicates the (department, pay_band) pairs.

10. Step 8 — ORDER BY: Sorting the Final Result

ORDER BY sorts the complete result set produced by all previous steps. Because it runs after SELECT, it can reference SELECT aliases by name — unlike WHERE, GROUP BY, and HAVING. It can also reference column positions (ORDER BY 2 means the second column in SELECT).

SELECT
    name,
    salary,
    department,
    salary * 1.10 AS projected_salary    -- defined in Step 6
FROM employees
ORDER BY
    projected_salary DESC,               -- alias visible in ORDER BY (Step 8)
    department ASC;

OUTPUT: ORDER BY uses SELECT alias — valid because ORDER BY runs after SELECT

11. Step 9 — LIMIT / TOP / FETCH FIRST: Restricting Rows

LIMIT (MySQL, PostgreSQL), TOP (SQL Server), and FETCH FIRST n ROWS ONLY (SQL Standard / Oracle) are applied absolutely last — after every other clause has processed. They slice the sorted result to return only the requested number of rows.

-- MySQL / PostgreSQL
SELECT   name, department, salary
FROM     employees
WHERE    status = 'Active'
ORDER BY salary DESC
LIMIT    3;                        -- Applied last: take top 3 after ORDER BY
-- SQL Server
SELECT TOP 3 name, department, salary
FROM     employees
WHERE    status = 'Active'
ORDER BY salary DESC;
-- SQL Standard / Oracle 12c+
SELECT   name, department, salary
FROM     employees
WHERE    status = 'Active'
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

OUTPUT: LIMIT 3 — only the top 3 rows after all steps are complete

LIMIT / TOP applied without ORDER BY returns an arbitrary set of rows — the database has no guaranteed row order without ORDER BY. Always pair LIMIT with ORDER BY in production queries.

12. Alias Visibility Rules — A Critical Reference

The most common SQL errors caused by execution order relate to alias visibility. Here is the complete reference:

Workaround Patterns for Alias Reuse

-- Problem: cannot use alias in WHERE or GROUP BY
SELECT salary * 1.1 AS projected FROM employees WHERE projected > 80000; -- ERROR
-- Solution 1: Wrap in a subquery
SELECT * FROM (
    SELECT name, salary, salary * 1.1 AS projected FROM employees
) sub
WHERE projected > 80000;
-- Solution 2: Use a CTE (cleaner)
WITH base AS (
    SELECT name, salary, salary * 1.1 AS projected FROM employees
)
SELECT * FROM base WHERE projected > 80000;
-- Solution 3: Repeat the expression (not ideal but simple)
SELECT name, salary, salary * 1.1 AS projected
FROM   employees
WHERE  salary * 1.1 > 80000;     -- repeat the expression

13. Full Query Walkthrough — All 9 Steps

Let us trace a complex query through every single step to see exactly how the database processes it.

The Query

SELECT
    d.dept_name,
    COUNT(e.emp_id)      AS headcount,
    AVG(e.salary)        AS avg_salary,
    MAX(e.salary)        AS top_salary
FROM       employees  e
INNER JOIN departments d ON e.dept_id = d.id
WHERE      e.hire_year >= 2019
GROUP BY   d.dept_name
HAVING     COUNT(e.emp_id) >= 2
ORDER BY   avg_salary DESC
LIMIT      3;

Step-by-Step Trace

Final Output

OUTPUT: Complete query result after all 9 execution steps

14. CTEs and Subqueries in the Execution Order

Common Table Expressions (CTEs) and subqueries extend the execution order model. A subquery in the FROM clause is materialised first as a virtual table, before the outer query’s FROM step begins.

-- CTE is executed first, before the outer query's FROM
WITH dept_stats AS (
    SELECT   dept_id,
             AVG(salary) AS avg_salary,    -- Step 6 of the INNER query
             COUNT(*)    AS headcount
    FROM     employees
    GROUP BY dept_id                        -- Step 4 of the INNER query
)
-- Outer query execution begins here
SELECT   e.name, e.salary, ds.avg_salary,
         e.salary - ds.avg_salary AS diff_from_avg
FROM     employees e                        -- Step 1 of OUTER query
JOIN     dept_stats ds ON e.dept_id = ds.dept_id  -- Step 2
WHERE    e.salary > ds.avg_salary           -- Step 3
ORDER BY diff_from_avg DESC;                -- Step 8

OUTPUT: Employees earning above their department average

15. Common Mistakes Caused by Execution Order

16. Quick Reference — Order of Execution Cheat Sheet

17. Conclusion

The logical order of SQL execution is one of those foundational concepts that, once understood, makes everything else click into place. It explains why aliases work in ORDER BY but not in WHERE, why HAVING can filter on aggregates but WHERE cannot, and why LIMIT always takes the last slice of an already-sorted result.

Memorise the nine steps: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Notice that SELECT — which you always write first — is only the sixth step. The database has already done the heavy lifting of sourcing, joining, filtering, and grouping by the time it decides which columns to project.

Apply this knowledge when debugging unexpected errors or wrong results: trace which step is failing, check whether you are using a construct (alias, aggregate) in a clause that runs before it is available, and use a CTE or subquery to promote it to the right execution level.

Happy Querying!