You write a query in the order SQL's grammar demands — SELECT first, then FROM, then the rest. The database does not evaluate it in that order. Knowing the order it does use, the logical order of execution, is one of the most useful things in SQL: it explains a whole class of errors ("unknown column" for an alias you just defined), it tells you where each condition belongs, and it is a favourite interview question. This lesson also follows a query through the database — parser, optimizer, executor — so that "the database decides how" stops being magic.

Written order and logical order

The clauses of a full SELECT are written in this order:

SELECT   DISTINCT columns, expressions
FROM     tables and joins
WHERE    row conditions
GROUP BY grouping columns
HAVING   group conditions
ORDER BY sort keys
LIMIT    count;

They are evaluated, logically, in this one:

Read it as a pipeline: each step takes the previous step's rows and hands its own on. GROUP BY and HAVING are the subject of the aggregation module; the rest you can use already.

Consequence 1: aliases do not exist in WHERE

SELECT name, price * qty AS line_total
FROM order_items
WHERE line_total > 100;          -- error: Unknown column 'line_total' in 'where clause'

WHERE is step 2; the alias line_total is created in step 5. When the rows are being filtered there is no such column. Repeat the expression instead:

SELECT name, price * qty AS line_total
FROM order_items
WHERE price * qty > 100;

The database computes the product once per row either way; repeating it costs nothing. (Later modules show the other fix — compute the alias in a subquery or a CTE, then filter on it outside.)

Consequence 2: aliases do exist in ORDER BY

SELECT name, price * qty AS line_total
FROM order_items
WHERE price * qty > 100
ORDER BY line_total DESC;

ORDER BY is step 7, after SELECT has named its columns, so the alias is visible. This holds in every major database. (MySQL is also lenient enough to accept select aliases in GROUP BY and HAVING; PostgreSQL accepts them in GROUP BY but not HAVING; SQL Server accepts neither. Portable code repeats the expression there too.)

Consequence 3: ORDER BY can see columns you did not select

ORDER BY runs after SELECT, yet it may sort by a column that is not in the select list:

SELECT name FROM students ORDER BY year DESC, name;

That works because, logically, the rows still carry all their columns until the very end — the select list decides what is shown. The exception: after DISTINCT or GROUP BY the original rows have been merged, so ORDER BY may only use what survived.

Consequence 4: LIMIT is last

LIMIT 3 takes the first three rows of the sorted result. It does not take three rows and then sort them. That is why ORDER BY … LIMIT answers "top three", and why LIMIT without ORDER BY answers "any three".

Consequence 5: WHERE sees rows, not results

WHERE decides about one input row at a time, before anything is computed across rows. A condition such as "the price is above the average price" cannot be written as WHERE price > AVG(price) — at step 2 there is no average yet. Aggregates belong to HAVING (about groups) or to a subquery. You will meet both; the error message Invalid use of group function means exactly this.

A worked trace

SELECT name, price * qty AS line_total
FROM order_items
WHERE price * qty >= 100
ORDER BY line_total DESC, item_id;

with

1. FROM — the four rows.
2. WHERE price * qty >= 100 — pen gives 50, dropped; lamp 120, desk 150, ink 120 kept.
3. SELECT — each kept row becomes (name, line_total), but still remembers its item_id.
4. ORDER BY line_total DESC, item_id — desk 150; then lamp and ink tie on 120, and item_id puts lamp (2) before ink (4).