Skip to main content

SQL Query Execution Order

Medium

Interview Question: "What is the exact execution order of a SQL query? Why does using a column alias defined in SELECT inside the WHERE clause fail, and where do window functions execute?"

Although a SQL query is written starting with SELECT, the database execution engine evaluates clauses in a vastly different, deterministic logical sequence. Understanding this sequence is fundamental for debugging queries, optimizing join operations, and structuring window functions.

The Logical SQL Execution Pipeline​

1. FROM / JOIN     -> Identify tables, evaluate ON predicates, build virtual dataset
2. WHERE           -> Filter individual rows prior to grouping
3. GROUP BY        -> Collapse rows into summary buckets
4. HAVING          -> Filter aggregated groups
5. SELECT          -> Compute output expressions and assign column ALIASES
6. WINDOW          -> Evaluate window functions (OVER (PARTITION BY ... ORDER BY ...))
7. DISTINCT        -> Eliminate duplicate rows
8. ORDER BY        -> Sort final rows (can access SELECT aliases)
9. LIMIT / OFFSET  -> Slice row window for pagination

Why Aliases Fail in WHERE​

Consider this classic error:

-- FAILS: column "annual_comp" does not exist
SELECT 
    employee_id, 
    salary * 12 AS annual_comp
FROM employees
WHERE annual_comp > 150000;

The Reason:​

  1. FROM employees evaluates first (Step 1).
  2. WHERE annual_comp > 150000 evaluates second (Step 2).
  3. SELECT ... AS annual_comp evaluates in Step 5.

When the engine filters rows in Step 2, the identifier annual_comp has not yet been registered in the engine's symbol table.

The Idiomatic Solutions:​

Repeat the arithmetic expression or wrap the query inside a Common Table Expression (CTE):

-- Option 1: Direct expression repetition
SELECT employee_id, salary * 12 AS annual_comp
FROM employees
WHERE (salary * 12) > 150000;

-- Option 2: Common Table Expression (CTE)
WITH RankedSalaries AS (
    SELECT employee_id, salary * 12 AS annual_comp
    FROM employees
)
SELECT * 
FROM RankedSalaries
WHERE annual_comp > 150000;

Why Aliases Work in ORDER BY​

In contrast, ORDER BY runs at Step 8, well after SELECT (Step 5) has evaluated the expression and registered the alias annual_comp:

SELECT employee_id, salary * 12 AS annual_comp
FROM employees
ORDER BY annual_comp DESC; -- Valid and standard ANSI SQL

Where Do Window Functions Execute?​

A frequent senior interview trap: "Can you filter by a window function directly in a WHERE or HAVING clause?"

-- SYNTAX ERROR: window functions are not allowed in WHERE
SELECT employee_id, department_id, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1;

Why it fails:​

Window functions are evaluated in Step 6 (during the projection phase, after HAVING has finished grouping rows, but before DISTINCT and ORDER BY). Because WHERE (Step 2) and HAVING (Step 4) run before windowing occurs, the engine cannot filter on an uncalculated window partition.

Solution: Always wrap window calculations in a CTE or derived table before applying row filters.

Logical Order vs. Physical Execution Plan​

Candidates often mistakenly assume hardware physically executes disks scans in this literal sequence:

  1. Logical Execution Order: The theoretical, semantic contract defined by the ANSI SQL standard that dictates variable scope and visibility.
  2. Physical Execution Plan: The actual operational tree generated by the Cost-Based Optimizer (CBO). The optimizer frequently reorders operations as long as the mathematical result remains unchanged:
    • Predicate Pushdown: Filters in WHERE are pushed down directly into the physical index scan before executing expensive JOIN operations.
    • Join Reordering: Smaller tables are hashed into memory first to perform hash joins against larger tables.
    • Top-N Sorts: ORDER BY ... LIMIT 5 uses a bounded in-memory min/max heap instead of sorting the entire table.

The ELI5 Analogy: Building a Custom Sandwich​

  1. FROM: Take the bread and fillings out of the fridge.
  2. WHERE: Discard any spoiled ingredients before preparing.
  3. GROUP BY: Stack the sandwich into layers.
  4. HAVING: Reject any sandwich stack that weighs under 300g.
  5. SELECT: Stick the label "Super Deluxe Sub" on the wrapper.
  6. WINDOW: Rank the sandwiches relative to other sandwiches on the table.
  7. DISTINCT: Remove identical duplicate sandwiches.
  8. ORDER BY: Sort the sandwiches from heaviest to lightest.
  9. LIMIT: Hand the first 2 sandwiches to the customer.

You cannot tell someone in Step 2 to "throw away sandwiches labeled 'Super Deluxe Sub'", because the label hasn't been written yet!

Summary​

"SQL executes in the logical sequence: FROM/JOIN →\to WHERE →\to GROUP BY →\to HAVING →\to SELECT →\to WINDOW →\to DISTINCT →\to ORDER BY →\to LIMIT. Aliases defined in SELECT are invisible to WHERE because filtering precedes projection. Window functions execute after HAVING and require a CTE or subquery to filter."

Crucial Nuance: Database Vendor Deviations​

While ANSI SQL strictly forbids using SELECT aliases prior to ORDER BY, modern engines like MySQL and SQLite permit aliases in GROUP BY and HAVING via proprietary syntax extensions. However, in PostgreSQL, Oracle, and Microsoft SQL Server, doing so triggers an immediate syntax error. Writing portable SQL requires repeating the expression or wrapping it in a CTE.

Code Demonstration: Filtering Aliases and Window Functions​

-- Setup: Sample table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT NOT NULL,
    salary NUMERIC(10, 2) NOT NULL
);

INSERT INTO employees VALUES 
(1, 10, 95000.00),
(2, 10, 80000.00),
(3, 20, 110000.00),
(4, 20, 105000.00);

-- -------------------------------------------------------------
-- 1. INCORRECT: Filtering on Window Function directly
-- -------------------------------------------------------------
-- SELECT employee_id, salary
-- FROM employees
-- WHERE ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1;
-- ERROR: Window functions are not allowed in WHERE

-- -------------------------------------------------------------
-- 2. CORRECT: Filtering via Common Table Expression (CTE)
-- -------------------------------------------------------------
WITH RankedEmployees AS (
    SELECT 
        employee_id,
        department_id,
        salary,
        salary * 12 AS annual_comp,
        ROW_NUMBER() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS rank_in_dept
    FROM employees
)
SELECT 
    employee_id,
    department_id,
    annual_comp
FROM RankedEmployees
WHERE rank_in_dept = 1 
  AND annual_comp > 1000000.00
ORDER BY annual_comp DESC;