By the end of this lesson you can list the logical order of the clauses of a query, and use it to predict which aliases are visible where and what a query returns.
Why it matters: You write SELECT first, but the database works on it later. Knowing the real order turns confusing errors ("column does not exist" for an alias you just defined) into obvious ones, and it's the mental model behind every complex query you'll write.
Dataset: Starter shop. Examples run in the editor on the right.
SQL is written in an order that reads like English: select these columns, from this table, where this is true, ordered like this. But PostgreSQL can't pick columns before it knows which table and which rows it's working with. So it evaluates the clauses in a different, logical order:
| step | clause | what happens to the rows |
|---|---|---|
| 1 | FROM | read every row of the table |
| 2 | WHERE | keep only rows where the condition is true |
| 3 | SELECT | compute the output columns, expressions and aliases |
| 4 | ORDER BY | sort the remaining rows |
| 5 | LIMIT | keep the first n rows |
Think of it as an assembly line: each step receives the rows the previous step produced.
Real queries can also have GROUP BY and HAVING, which form groups of rows (for totals per city, say). They run after WHERE and before SELECT. You'll learn them in the aggregation track; the visual below includes them so you can see the full line. Step through it and watch the row count change at each stage.
SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY revenue DESC
LIMIT 1;FROM: Read the table: all rows.
| city | status | amount |
|---|---|---|
| Riyadh | delivered | 120 |
| Jeddah | cancelled | 90 |
| Riyadh | delivered | 60 |
| Dammam | delivered | 200 |
| Jeddah | delivered | 75 |
| Riyadh | delivered | 40 |
| Dammam | delivered | 30 |
You write SELECT first, but PostgreSQL logically evaluates it after FROM, WHERE, GROUP BY and HAVING. That's why WHERE can't use a SELECT alias, while ORDER BY can.
Filtering rows with WHERE
You've used WHERE column = value. Conditions can also compare: > (greater than), < (less than), >=, <= and <> (not equal). Text values go in single quotes. The next track covers conditions thoroughly; these are enough to see the order of evaluation in action.
SELECT product_name,
price
FROM products
WHERE category = 'Home'
ORDER BY price DESC
LIMIT 2;FROM reads all 10 products; WHERE keeps the 3 in the Home category; SELECT picks two columns; ORDER BY puts the most expensive first; LIMIT keeps 2. Result: the two priciest Home products.
Consequence 1: WHERE can't see SELECT aliases
An alias is created at step 3 (SELECT). WHERE runs at step 2, before the alias exists. So this fails:
SELECT product_name, price * stock AS stock_value
FROM products
WHERE stock_value > 3000; -- error: column "stock_value" does not existThe fix is to repeat the expression in WHERE: WHERE price * stock > 3000. (Later you'll meet subqueries and CTEs, which let you name a calculation once and reuse it.)
SELECT product_name,
price * stock AS stock_value
FROM products
WHERE price * stock > 3000;WHERE computes price * stock for each row to decide whether to keep it; SELECT then computes it again for the rows that survived and names it stock_value. Four products hold more than SAR 3,000 of stock.
Consequence 2: ORDER BY can use aliases
ORDER BY runs at step 4, after SELECT, so the alias exists by then. ORDER BY stock_value DESC works. ORDER BY can also sort by a column you didn't select, because the full row is still available.
Consequence 3: LIMIT comes last
LIMIT cuts the result after sorting. That's why ORDER BY price DESC LIMIT 3 gives the three most expensive products, not three random products that are then sorted.
SELECT product_name,
price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 3;SELECT has already created stock_value when ORDER BY runs, so sorting by it works. The three products with the most money tied up in stock come first.