Query Execution Order

SQL: lesson 9 of 10

SQL is written top-down but evaluated in a different order.

Lesson 9 of 10 · 5 min

Step 1 of 9

written

SELECTFROMWHEREGROUPHAVINGORDER

You write SELECT first. The engine runs it fifth, and that explains most beginner errors.

The Idea

You write SELECT first, but the engine runs it fifth. The real order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. That single fact explains most beginner errors: why WHERE cannot see a column alias, and why an aggregate filter has to live in HAVING.

Real-World Example

A cider press works the same way. Apples arrive from the orchard, rotten fruit is thrown out, the rest is tipped into bins by variety, bins too small to press are set aside, and only then is each pressing labelled and lined up — heaviest first.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE apple (variety TEXT, kilos REAL, rotten INT);
  INSERT INTO apple VALUES ('Dabinett',60,0), ('Dabinett',40,0),
    ('Kingston',30,0), ('Kingston',90,1), ('Yarlington',55,0);""")
print(db.execute("""
  SELECT variety, SUM(kilos) AS total   -- 5th: build the output row
  FROM apple                            -- 1st: choose the source
  WHERE rotten = 0                      -- 2nd: bin the bad fruit
  GROUP BY variety                      -- 3rd: one bin per variety
  HAVING SUM(kilos) >= 50               -- 4th: skip small pressings
  ORDER BY total DESC                   -- 6th: heaviest first
""").fetchall())
# [('Dabinett', 100.0), ('Yarlington', 55.0)]

Your turn

Put the steps in the right order.

GROUP BY — fold the survivors into one row per variety
SELECT — compute the columns actually asked for
FROM — read rows out of the source table
WHERE — drop individual rows before any grouping
ORDER BY — sort the finished rows
HAVING — drop whole groups using their aggregate

Mini quiz

1 / 3

Query Execution Order