SQL order of execution: how a query is evaluated

You write a query starting with SELECT, but that is not where the engine starts. It first works out which rows it has (FROM), filters them (WHERE), groups them (GROUP BY), filters the groups (HAVING), and only then computes the columns you asked for. Knowing this logical order explains a whole family of errors at once: why an alias works in one clause and not in another, why an aggregate cannot go in WHERE, why LIMIT sees sorted rows.

The scripts are recorded in SQLite 3.49.1 (sql.js 1.14.2 in your browser), and the notes add what PostgreSQL and MySQL do with the same queries.

The order, step by step

Each step works on what the step before it produced:

1. FROM produces the input rows: a table here, the result of joins later in this track.
2. WHERE keeps the rows that pass its condition. It sees one row at a time.
3. GROUP BY puts the rows into groups and computes the aggregates of each group.
4. HAVING keeps the groups that pass its condition.
5. SELECT computes the output columns, gives them their aliases and computes any window functions.
6. DISTINCT removes duplicate output rows.
7. ORDER BY sorts the rows.
8. LIMIT and OFFSET keep a slice of the sorted rows.

PostgreSQL’s manual describes the processing of SELECT in this order, and SQLite’s describes the same sequence for its part of it. When UNION and the other set operations appear, they combine whole results between steps 6 and 7.

Logical, not physical

This is the order of the meaning of a query, not a description of what the engine does internally. A planner is free to run things differently, for example to use an index so that it never reads the rows WHERE would reject, or to stop reading once LIMIT is satisfied, as long as the result is the one this order defines. PostgreSQL’s manual is explicit about it: its planner weighs several possible plans for one query, all with the same answer, and runs the one it estimates will finish soonest. So the order tells you what a query means and what each clause can see; it does not tell you how fast it runs.

One query, rebuilt step by step

The first query below answers “which two customers paid the most, among those with at least two paid orders?”. The queries after it rebuild it one step at a time, so you can see what each step hands to the next:

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- The full query: the two customers with the highest paid totals, among those with
-- at least two paid orders. Below it, the same query is rebuilt one step at a time.
SELECT customer, count(*) AS paid_orders, sum(amount_paise) / 100.0 AS paid_rupees
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING count(*) >= 2
ORDER BY paid_rupees DESC
LIMIT 2;

-- Steps 1 and 2, FROM and WHERE: the rows that survive the filter.
SELECT id, customer, amount_paise FROM orders WHERE status = 'paid';

-- Step 3, GROUP BY: one row per customer, with the aggregates computed.
SELECT customer, count(*) AS paid_orders, sum(amount_paise) / 100.0 AS paid_rupees
FROM orders WHERE status = 'paid'
GROUP BY customer;

-- Step 4, HAVING: whole groups are dropped; the SELECT list and its aliases come next.
SELECT customer, count(*) AS paid_orders, sum(amount_paise) / 100.0 AS paid_rupees
FROM orders WHERE status = 'paid'
GROUP BY customer
HAVING count(*) >= 2;

┌──────────┬─────────────┬─────────────┐
│ customer │ paid_orders │ paid_rupees │
├──────────┼─────────────┼─────────────┤
│ Asha     │ 3           │ 1620.0      │
│ Meera    │ 2           │ 340.0       │
└──────────┴─────────────┴─────────────┘
┌────┬──────────┬──────────────┐
│ id │ customer │ amount_paise │
├────┼──────────┼──────────────┤
│ 1  │ Asha     │ 45000        │
│ 2  │ Vikram   │ 12000        │
│ 4  │ Meera    │ 8000         │
│ 5  │ Asha     │ 99000        │
│ 6  │ Vikram   │ 15000        │
│ 7  │ Tenzin   │ 52000        │
│ 9  │ Meera    │ 26000        │
│ 10 │ Asha     │ 18000        │
└────┴──────────┴──────────────┘
┌──────────┬─────────────┬─────────────┐
│ customer │ paid_orders │ paid_rupees │
├──────────┼─────────────┼─────────────┤
│ Asha     │ 3           │ 1620.0      │
│ Meera    │ 2           │ 340.0       │
│ Tenzin   │ 1           │ 520.0       │
│ Vikram   │ 2           │ 270.0       │
└──────────┴─────────────┴─────────────┘
┌──────────┬─────────────┬─────────────┐
│ customer │ paid_orders │ paid_rupees │
├──────────┼─────────────┼─────────────┤
│ Asha     │ 3           │ 1620.0      │
│ Meera    │ 2           │ 340.0       │
│ Vikram   │ 2           │ 270.0       │
└──────────┴─────────────┴─────────────┘

• After FROM and WHERE, 8 of the 10 orders are left: the two cancelled ones are gone.
• GROUP BY customer turns them into 4 groups, one per customer, with the count and the total of each. (Their order is not guaranteed yet.)
• HAVING count(*) >= 2 drops Tenzin’s group, which has one paid order.
• Only then do the aliases paid_orders and paid_rupees exist, so ORDER BY paid_rupees DESC can use one, and LIMIT 2 keeps the first two sorted rows: Asha and Meera. Vikram had two paid orders, but a smaller total.

Where an alias can be used

An alias is created in step 5. Clauses that run after SELECT can use it; clauses that run before cannot, in principle. The engines follow that principle with a few extensions of their own:

ORDER BY works everywhere because it runs after SELECT. GROUP BY and HAVING run before it, and accepting an alias there is an extension: PostgreSQL allows it in GROUP BY only, MySQL and SQLite in both. WHERE runs earlier still, before any grouping; MySQL’s manual gives exactly that reason for refusing aliases there, because the value may not have been computed yet when the row is tested.

SQLite’s alias in WHERE, and its trap

SQLite goes one step further: when a name in WHERE matches no column, it tries the aliases of the SELECT list. That is convenient, and it hides a trap:

-- Amounts in paise.
CREATE TABLE payments (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO payments VALUES (1, 45000), (2, 800), (3, 120000);

-- SQLite looks an unknown name in WHERE up among the SELECT aliases.
-- PostgreSQL and MySQL reject this query.
SELECT id, amount / 100.0 AS rupees FROM payments WHERE rupees > 400;

-- But a column always wins over an alias: this WHERE compares paise, not rupees,
-- in every engine. Payment 2 (8 rupees) is returned although 8 is not above 400.
SELECT id, amount / 100.0 AS amount FROM payments WHERE amount > 400;

┌────┬────────┐
│ id │ rupees │
├────┼────────┤
│ 1  │ 450.0  │
│ 3  │ 1200.0 │
└────┴────────┘
┌────┬────────┐
│ id │ amount │
├────┼────────┤
│ 1  │ 450.0  │
│ 2  │ 8.0    │
│ 3  │ 1200.0 │
└────┴────────┘

The first query works in SQLite only; PostgreSQL and MySQL report that rupees is not a column. The second query runs in all three engines and returns the wrong rows in all three: the alias amount has the same name as a column, and inside WHERE the column wins, so the filter compares paise with 400. Payment 2 is 800 paise, which is 8 rupees, and it slips through. Never give an alias the name of a column that the same query also filters on.

The portable fixes are to repeat the expression, or to compute it in a subquery so that the outer WHERE runs after it:

CREATE TABLE payments (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO payments VALUES (1, 45000), (2, 800), (3, 120000);

-- Portable fix 1: repeat the expression in WHERE.
SELECT id, amount / 100.0 AS rupees FROM payments WHERE amount / 100.0 > 400;

-- Portable fix 2: compute the alias in a subquery; the outer WHERE runs after it.
SELECT id, rupees
FROM (SELECT id, amount / 100.0 AS rupees FROM payments) AS p
WHERE rupees > 400;

┌────┬────────┐
│ id │ rupees │
├────┼────────┤
│ 1  │ 450.0  │
│ 3  │ 1200.0 │
└────┴────────┘
┌────┬────────┐
│ id │ rupees │
├────┼────────┤
│ 1  │ 450.0  │
│ 3  │ 1200.0 │
└────┴────────┘

Five errors the order explains

Each of these queries asks a clause to use something that only exists in a later step. SQLite rejects all five; the errors appear in the same order as the queries:

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- Five queries that put something in a clause that runs too early to see it.

-- 1. An aggregate in WHERE: WHERE runs before any group exists.
SELECT customer, count(*) FROM orders WHERE count(*) >= 2 GROUP BY customer;

-- 2. The same through an alias: n stands for count(*), which WHERE cannot use.
SELECT customer, count(*) AS n FROM orders WHERE n >= 2 GROUP BY customer;

-- 3. A window function in WHERE: windows are computed after WHERE, GROUP BY and HAVING.
SELECT id, row_number() OVER (ORDER BY amount_paise DESC) AS place
FROM orders
WHERE place <= 3;

-- 4. An aggregate in GROUP BY: the groups must exist before they can be counted.
SELECT count(*) FROM orders GROUP BY count(*);

-- 5. An alias used in the same SELECT list: the list is computed as one step.
SELECT amount_paise / 100.0 AS rupees, rupees * 0.05 AS delivery_fee FROM orders;

Parse error near line 24: misuse of aggregate: count()
Parse error near line 27: misuse of aggregate: count()
Parse error near line 30: misuse of aliased window function place
Parse error near line 35: aggregate functions are not allowed in the GROUP BY clause
Parse error near line 38: no such column: rupees

1. WHERE count(*) >= 2: aggregates are computed in step 3, after WHERE. Put the condition in HAVING.
2. WHERE n >= 2, with n the alias of count(*): SQLite looks the alias up, finds an aggregate, and fails as in 1.
3. WHERE place <= 3 on a window function: windows are computed in step 5, after WHERE, GROUP BY and HAVING.
4. GROUP BY count(*): the groups have to exist before anything can be counted in them.
5. rupees * 0.05 in the same SELECT list as AS rupees: the list is computed as one step, so no column of it can use another’s alias. Repeat the expression.

PostgreSQL and MySQL reject all five as well. For 2 and 3 their reason is simpler: they do not look at aliases in WHERE at all, so n and place are unknown columns there.

Filtering on a window function

Window functions get their own module later, but the order already tells you how to filter on one: compute it in a subquery, then filter in the outer query, whose WHERE runs after the inner query has finished.

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- To filter on a window function, compute it in a subquery and filter outside.
SELECT id, customer, amount_paise, place
FROM (SELECT id, customer, amount_paise,
             row_number() OVER (ORDER BY amount_paise DESC) AS place
      FROM orders) AS ranked
WHERE place <= 3
ORDER BY place;

┌────┬──────────┬──────────────┬───────┐
│ id │ customer │ amount_paise │ place │
├────┼──────────┼──────────────┼───────┤
│ 5  │ Asha     │ 99000        │ 1     │
│ 7  │ Tenzin   │ 52000        │ 2     │
│ 1  │ Asha     │ 45000        │ 3     │
└────┴──────────┴──────────────┴───────┘

DISTINCT, ORDER BY and LIMIT

The last three steps explain why “top N” queries work: the rows are made distinct, then sorted, and only then cut.

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- DISTINCT runs before ORDER BY, and LIMIT runs last.
SELECT DISTINCT city
FROM orders
ORDER BY city
LIMIT 2;

┌────────┐
│  city  │
├────────┤
│ Jaipur │
│ Kochi  │
└────────┘

The ten orders come from three cities. DISTINCT leaves three rows, ORDER BY city sorts them, and LIMIT 2 keeps the first two. If LIMIT came first, it would keep two arbitrary orders before DISTINCT and ORDER BY saw them: the answer could be a single city (two orders from Pune) or two cities that are not the first two in alphabetical order.

Key takeaways

• A SELECT is evaluated in the logical order FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT, whatever order it is written in. The planner may work differently, but the result must match this order.
• A clause can only use what earlier steps produced: no aggregates in WHERE, no window functions in WHERE or HAVING, no alias in its own SELECT list.
• An alias works on its own in ORDER BY and in GROUP BY in all three engines; in HAVING only in MySQL and SQLite; in WHERE only in SQLite, and only when no column has that name and the alias stands for a plain expression, not an aggregate or a window function.
• A column with the same name as an alias wins in WHERE, in every engine, so do not reuse column names as aliases.
• To filter on an alias or a window function portably, repeat the expression or compute it in a subquery.