SQL Query Execution Order: Why SELECT Runs Almost LastBrief · digest for November 5
Brief≈ 1 min

SQL Query Execution Order: Why SELECT Runs Almost Last

SQL's declarative syntax often creates an illusion: we start a query with the SELECT keyword, listing the columns we want. Yet in the PostgreSQL Query Path documentation, the logical execution pipeline is wired differently.

The relational engine processes a query in this chain:

  1. FROM and JOIN: base table reads and joins;
  2. WHERE: filtering rows before aggregations;
  3. GROUP BY and HAVING: grouping and filtering groups;
  4. SELECT: evaluating expressions and assigning aliases;
  5. DISTINCT, ORDER BY, and LIMIT: deduplication, sorting, and slicing.

That is why trying to reference a SELECT alias inside a WHERE clause fails with an error:

-- ERROR: column "bonus" does not exist
SELECT salary * 0.2 AS bonus 
FROM employees 
WHERE bonus > 1000;

At step 2, the engine has not reached the SELECT projection (step 4) yet and does not know the name bonus.

As we noted in our breakdown of SQL execution order, the fix is simple: repeat the expression or move it into a CTE:

WITH payroll AS (
  SELECT id, salary * 0.2 AS bonus FROM employees
)
SELECT * FROM payroll WHERE bonus > 1000;
Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 2