Module 1: Start here · Lesson 1.4 · about 20 min
How a query runs: the order SQL thinks in
You write SELECT first, but SQL runs FROM first. Watch a query run step by step on real tables, and learn the order that explains most beginner errors.
Module 1 · about 20 minutes · read first: 1.3. You’ll be able to say the order in which SQL works out each part of a query, and use it to fix the most common beginner errors.
In everyday terms: a recipe card lists the dish’s name first, but the cook starts by fetching ingredients. SELECT is the name on the card; FROM is the trip to the cupboard.
1. Why this matters
Most “why doesn’t this work?” moments in SQL come from one thing: the order you write a query in is not the order the database runs it in. Once you know the running order, error messages make sense, and joins and GROUP BY stop feeling like magic. Interviewers ask about it too.
2. Written order vs running order
You always write the parts of a query in this order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database runs them in a different order:
The order you write
SELECTFROMJOINWHEREGROUP BYHAVINGORDER BYLIMIT
The order SQL runs it
FROM / JOINget the rows and glue tables togetherWHEREkeep only rows that passGROUP BYgather rows into groupsHAVINGkeep only groups that passSELECTpick and work out the columnsDISTINCTdrop copiesORDER BYsortLIMITkeep the first few
Think of a kitchen. FROM is fetching ingredients, WHERE is throwing away what you won’t use, GROUP BY is putting the rest into bowls, SELECT is plating up, ORDER BY is lining up the plates, and LIMIT is serving only the first few.
Maths and principle
Principle: each step takes a table and hands a new table to the next step. A query is a pipeline of table-to-table functions.
Equation: result = LIMIT( ORDER( SELECT( HAVING( GROUP( WHERE( FROM ) ) ) ) ) ). As with functions in maths, the innermost one is worked out first, even though SELECT is written first.
Analogy: function composition, f(g(x)): you work out g before f. It breaks down a little because the database is free to optimise (for example, filter early while it reads), but the answer is always the same as if it ran in this order.
Try it: in SELECT name FROM customers WHERE city = 'Ottawa' ORDER BY name, which runs first, WHERE or ORDER BY? (WHERE.)
3. Watch it run
Press Run to see the answer. Then press Show how it runs and Play: the join matches each order with its customer, WHERE throws rows away, GROUP BY colours the groups, and SUM squeezes each group into one row.
-- try
SELECT c.city, SUM(o.amount) AS total
FROM mini_orders o
JOIN mini_customers c ON o.customer_id = c.id
WHERE o.amount > 10
GROUP BY c.city
ORDER BY total DESC
Things to notice:
- Order 106 belongs to customer 9, who doesn’t exist, so the (inner) join drops it.
- WHERE removes the $5 bagels before grouping, so they never reach a total.
- Each city turns into one row only at the aggregate step.
- FROM / JOIN
- Runs first: fetch the rows of the table(s).
- WHERE
- Throws away rows, one at a time, before any grouping.
- GROUP BY
- Puts the rows left into groups.
- HAVING
- Throws away whole groups, after the totals are known.
- SELECT
- Works out the output columns and their aliases.
- ORDER BY, LIMIT
- Sort the answer, then keep the first few rows.
- Physical meaning
- Each step takes a table and hands a smaller or reshaped table to the next. A step can only use what earlier steps have made, which is why WHERE cannot see a SUM or (in most databases) a SELECT alias.
“SQL runs top to bottom, starting with SELECT.” SELECT is written first but runs fifth. That is why WHERE SUM(amount) > 40 fails (there are no groups yet when WHERE runs) and why ORDER BY can use an alias but WHERE usually cannot.
Watch the query above run, one step at a time (the live box’s Show how it runs button animates the real thing):
| order | city | amount |
|---|---|---|
| 101 | Toronto | 45 |
| 102 | Toronto | 5 |
| 103 | Ottawa | 20 |
| 104 | Toronto | 90 |
| 105 | Toronto | 45 |
| order | city | amount |
|---|---|---|
| 101 | Toronto | 45 |
| 103 | Ottawa | 20 |
| 104 | Toronto | 90 |
| 105 | Toronto | 45 |
| city | total |
|---|---|
| Toronto | 180 |
| Ottawa | 20 |
4. The errors this explains
An alias in WHERE. In most databases this fails, because WHERE runs before SELECT has made total:
-- noviz
SELECT order_id, amount * 2 AS total
FROM mini_orders
WHERE total > 50
Try it: SQLite (the database in this course) is forgiving and accepts it, but SQL Server, PostgreSQL and most other databases reject it. Write the expression again (WHERE amount * 2 > 50) so your SQL works everywhere.
A SUM in WHERE. This fails in every database, because WHERE tests one row at a time and a SUM needs a whole group:
-- fails
SELECT customer_id, SUM(amount) AS spent
FROM mini_orders
WHERE SUM(amount) > 40
GROUP BY customer_id
Read the error message, then fix it by moving the condition to HAVING, which runs after GROUP BY.
5. Worked example
Question: which cities have customers who spent more than $50 in total?
- FROM
mini_ordersjoined tomini_customers: 5 joined rows (order 106 has no customer). - No WHERE.
- GROUP BY city: Toronto (orders 101, 102, 104, 105), Ottawa (103).
- SUM per group: Toronto 45 + 5 + 90 + 45 = 185, Ottawa 20.
- HAVING SUM > 50 keeps Toronto only.
- SELECT city and the total. Answer: Toronto, 185.
6. Practice
-- exercise: Change the query so it lists each customer id with the number of orders they placed, but only customers with 2 or more orders.
-- hint: COUNT(*) counts rows in each group. A condition on a count goes in HAVING, not WHERE.
SELECT customer_id
FROM mini_orders
GROUP BY customer_id
-- answer:
SELECT customer_id, COUNT(*) AS orders
FROM mini_orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
-- exercise: List the item and amount of every order over $30, biggest first.
-- hint: WHERE amount > 30, then ORDER BY amount DESC.
SELECT item, amount
FROM mini_orders
-- answer:
SELECT item, amount
FROM mini_orders
WHERE amount > 30
ORDER BY amount DESC
- Written order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
- Running order: FROM (and JOIN), WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- WHERE filters rows before grouping; HAVING filters groups after.
- A step can only use what earlier steps made: no SUM in WHERE, aliases are safe in ORDER BY.
- When a query errors, walk through it in running order.
New words: logical order of execution, alias, pipeline.
Check yourself
Sources
- Microsoft Learn, SELECT (Transact-SQL): Logical processing order of the SELECT statement https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
- PostgreSQL documentation, Queries: Overview and Table Expressions https://www.postgresql.org/docs/current/queries-table-expressions.html
- SQLite documentation, SELECT https://www.sqlite.org/lang_select.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.