The order SQL runs your query
Why SQL runs FROM first and SELECT fifth, and the surprises that explains.
Lesson 29 of 35 · about 18 minutes
The lesson
You write a query top to bottom: SELECT first, then FROM, then WHERE, and so on. But the database doesn't run it in that order. It runs it in the order that makes sense for building the answer.
Think of a warehouse packing orders
Imagine a warehouse worker making up a delivery of books.
- FROM / JOIN: go to the right shelves and pull everything off (and if you need two shelves, put their items side by side).
- WHERE: throw out every item that doesn't match the order slip.
- GROUP BY: sort what's left into boxes, one box per group.
- HAVING: throw out whole boxes that don't qualify.
- SELECT: write the label for each item or box: which details to show, and any calculations.
- DISTINCT: remove any labels that are exact duplicates.
- ORDER BY: line everything up in the right order.
- LIMIT: take only the first few off the front of the line.
| You write it in this order | The database runs it in this order |
|---|---|
| SELECT | 1. FROM and JOIN |
| DISTINCT | 2. WHERE |
| FROM and JOIN | 3. GROUP BY |
| WHERE | 4. HAVING |
| GROUP BY | 5. SELECT |
| HAVING | 6. DISTINCT |
| ORDER BY | 7. ORDER BY |
| LIMIT | 8. LIMIT |
The one to remember: SELECT runs fifth, not first. Almost every surprise below comes from that.
Scenario 1: a simple query
SELECT title
FROM books
WHERE genre = 'Fantasy';
FROM fetches all 20 books. WHERE keeps the 3 Fantasy ones. SELECT keeps just the title. Result: Guards! Guards!, Small Gods, A Wizard of Earthsea.
Scenario 2: a nickname (alias) in ORDER BY works
SELECT title, price * 0.9 AS sale_price
FROM books
ORDER BY sale_price
LIMIT 3;
SELECT (step 5) creates the nickname sale_price. ORDER BY (step 7) runs later, so the nickname already exists. Result: The Blue Umbrella, Swami and Friends, Malgudi Days.
Scenario 3: a nickname in WHERE usually fails
SELECT title, price * 0.9 AS sale_price
FROM books
WHERE sale_price < 9;
WHERE is step 2. SELECT, which creates sale_price, hasn't run yet. In PostgreSQL, SQL Server and Oracle this is an error ("column sale_price does not exist"). Note: SQLite, which runs this course's practice boxes, and MySQL are lenient and allow it, so it will work here. Don't rely on it at work. Repeat the calculation instead: WHERE price * 0.9 < 9.
Scenario 4: a total in WHERE always fails
SELECT genre, COUNT(*)
FROM books
WHERE COUNT(*) > 2
GROUP BY genre;
Error: misuse of aggregate: COUNT(). WHERE (step 2) looks at one row at a time. Counting needs groups, and groups don't exist until GROUP BY (step 3). Filtering on a count is HAVING's job.
Scenario 5: WHERE and HAVING together
SELECT genre, COUNT(*) AS cheap_books
FROM books
WHERE price < 15
GROUP BY genre
HAVING COUNT(*) >= 3
ORDER BY cheap_books DESC;
- FROM: all 20 books.
- WHERE: keep books under $15 (17 books).
- GROUP BY: one box per genre (6 boxes).
- HAVING: keep boxes with 3 or more books (3 boxes).
- SELECT: show the genre and the count.
- ORDER BY: biggest count first.
Result: Fiction 6, then Children 3 and Fantasy 3.
WHERE filters rows before grouping. HAVING filters groups after grouping.
Scenario 6: JOIN happens first
SELECT a.name, COUNT(*) AS books
FROM books b
JOIN authors a ON a.id = b.author_id
WHERE b.price < 15
GROUP BY a.name
HAVING COUNT(*) >= 2
ORDER BY books DESC, a.name
LIMIT 3;
The JOIN is part of step 1: the two tables are joined into one wide table before anything else happens. That's why WHERE can use columns from both tables. Result: Ruskin Bond 3, Ursula K. Le Guin 3, Agatha Christie 2.
Scenario 7: the LEFT JOIN trap, ON versus WHERE
Every author, with their Fantasy books if they have any:
SELECT a.name, b.title
FROM authors a
LEFT JOIN books b ON b.author_id = a.id AND b.genre = 'Fantasy';
This returns all 10 authors. Those without Fantasy books show an empty title.
Move the genre check to WHERE, and the result changes:
SELECT a.name, b.title
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
WHERE b.genre = 'Fantasy';
Only 3 rows come back, and the authors without Fantasy books disappear. The LEFT JOIN (step 1) kept every author, but then WHERE (step 2) threw away the rows with an empty genre. A condition in ON shapes the join. A condition in WHERE filters afterwards.
Scenario 8: DISTINCT comes after SELECT
SELECT DISTINCT genre
FROM books
ORDER BY genre;
SELECT picks out the genre from all 20 rows (with repeats). DISTINCT then removes the duplicates, leaving 6. ORDER BY sorts them A to Z.
Scenario 9: LIMIT is always last
SELECT title, price
FROM books
ORDER BY price
LIMIT 3;
ORDER BY sorts all 20 books first, and only then does LIMIT take the top 3. So you really do get the 3 cheapest, not 3 random books sorted. Result: The Blue Umbrella $7.99, Swami and Friends $7.99, Malgudi Days $8.49.
Scenario 10: a subquery runs as its own query first
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC;
The query inside the brackets is a complete query of its own, with its own FROM and SELECT. It works out the average price ($12.39) first. Then the outer query runs in the normal order, using that number in its WHERE. Result: 11 books, from Sapiens at $18.99 down to Small Gods at $12.49.
A note on the planner
This is the logical order: the order the database promises your results will behave as if it followed. Behind the scenes, the planner from lesson 4 may take shortcuts, like using an index. It will never change the answer.
- The database runs a query in this order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
- SELECT runs fifth, so nicknames made in SELECT work in ORDER BY but not in WHERE (in most databases).
- WHERE filters rows before grouping. HAVING filters groups after grouping, so totals like COUNT belong in HAVING.