SQL Order of Operations: The Logical Execution Order Behind Every Query Trap

SQL is written from SELECT down, but one query block is reasoned about in a different order:

FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT and window functions → DISTINCT → ORDER BY → LIMIT

This is a logical model for understanding the result, not a promise about the physical plan PostgreSQL will execute. Its value is practical: it tells you which rows and names exist at each stage.

The logical processing map

Each CTE or subquery is a new query block with its own sequence. That reset is why an outer WHERE can filter an alias or window value created by an inner block.

FROM and WHERE: population and alias visibility

ON decides which rows match while the join is being built. A right-table condition in WHERE runs later and rejects a null-padded row because the condition is not true for NULL. Put the status condition in ON when every customer must remain in the output.

Input: customers

Input: orders

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Output

If the condition were WHERE o.status = 'paid', Cy's null-padded row would be removed. See SQL joins and SQL COUNT for the two separate decisions: preserving the population and counting only matched rows.

Alias visibility follows the same stage order. An output alias is created in SELECT, so a WHERE adjusted_amount >= 110 in that same block cannot refer to it. Give the calculation its own block, then the outer block can filter the ordinary column.

Input: sales

WITH priced AS (
  SELECT
    sale_id,
    rep,
    ROUND(amount * 1.10, 2) AS adjusted_amount
  FROM sales
)
SELECT sale_id, rep, adjusted_amount
FROM priced
WHERE adjusted_amount >= 110
ORDER BY sale_id;

Output

Repeating the calculation in WHERE would also be valid here. A second block is especially useful when the expression is long or when the value comes from a window function.

GROUP BY and HAVING: rows become groups

WHERE filters sale rows before grouping. HAVING filters the grouped totals afterward. This query first excludes sales below 60, then keeps only reps whose remaining total reaches 100.

Input: sales

SELECT
  rep,
  SUM(amount) AS qualifying_sales
FROM sales
WHERE amount >= 60
GROUP BY rep
HAVING SUM(amount) >= 100
ORDER BY qualifying_sales DESC, rep;

Output

Moving the row condition to HAVING would not be an equivalent rewrite. After grouping by rep, there is no single unaggregated amount for the clause to inspect. Likewise, WHERE SUM(amount) >= 100 is invalid because the aggregate does not exist at the WHERE stage.

Window functions see post-HAVING rows

Window functions are evaluated after grouping and group filtering in their query block. Here the inner query creates rep totals and removes totals below 100. The outer query ranks only the surviving groups.

Input: sales

WITH rep_totals AS (
  SELECT rep, SUM(amount) AS total_sales
  FROM sales
  GROUP BY rep
  HAVING SUM(amount) >= 100
)
SELECT
  rep,
  total_sales,
  DENSE_RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM rep_totals
ORDER BY sales_rank, rep;

Output

A window alias also cannot be filtered by WHERE in the same block. Compute it in a CTE or subquery and filter it from the outer block, just as the alias example did. The window-functions guide covers partitioning, ties, and frames.

DISTINCT, ORDER BY, and LIMIT finish the block

DISTINCT removes duplicate projected rows, ORDER BY sorts those rows, and LIMIT keeps the requested prefix. This query asks for the first two page names alphabetically, not for two arbitrary event rows.

Input: page_views

SELECT DISTINCT page_name
FROM page_views
ORDER BY page_name
LIMIT 2;

Output

With SELECT DISTINCT, PostgreSQL requires an ORDER BY expression to appear in the select list. After duplicate page names collapse, an omitted detail such as view_id no longer identifies a single value for each output row. Also give LIMIT a deterministic ORDER BY; without it, the database is not being asked for a stable subset. Practice these stage decisions with the SQL practice questions.

FAQ

Is logical order the same as execution order?

No. Logical order explains query semantics. PostgreSQL can push filters, choose different join algorithms, or reorder eligible joins while preserving the same result. Use EXPLAIN to inspect the selected physical plan.

Why can ORDER BY use a SELECT alias while WHERE cannot?

The alias exists after the SELECT stage. ORDER BY comes later in the logical block; WHERE comes earlier. A CTE or subquery turns the alias into an input column for a new block.

Does HAVING always require GROUP BY?

Not in PostgreSQL. Without GROUP BY, the input can be treated as one group, so HAVING can keep or remove that aggregate result. Use it when that one-group meaning matches the question.

Where should I filter a window result?

In an outer query block. Compute the window value in a CTE or subquery, then apply WHERE outside. That is not a workaround for a broken engine; it follows from the value first existing at the window stage.

What order should I memorize?

Memorize enough to trace row sets: join, filter rows, group, filter groups, calculate outputs and windows, deduplicate, sort, truncate. More importantly, practice naming what exists after each step. That reasoning catches more bugs than reciting clause names alone.