How a Query Is Processed

You write a query in one order, but the database processes it in a different, logical order. Knowing that order explains many rules and error messages.

Written order vs logical order

SELECT category, COUNT(*) AS products       -- 5
FROM products                               -- 1
WHERE price > 30                            -- 2
GROUP BY category                           -- 3
HAVING COUNT(*) >= 1                        -- 4
ORDER BY products DESC, category            -- 6
LIMIT 2;                                    -- 7

+-------------+----------+
|  category   | products |
+-------------+----------+
| Accessories |        3 |
| Audio       |        1 |
+-------------+----------+

1. FROM — pick the table (and join others, in Level 2).
2. WHERE — filter individual rows.
3. GROUP BY — form groups.
4. HAVING — filter groups.
5. SELECT — compute the output columns and aliases.
6. ORDER BY — sort the result.
7. LIMIT — keep the first rows.

What this explains

• Why WHERE can't use aggregates — it runs before groups exist.
• Why WHERE can't use a SELECT alias — the alias hasn't been created yet at step 2. (SQLite and MySQL allow it as an extension; PostgreSQL and standard SQL don't.)
• Why ORDER BY can use aliases — it runs after SELECT.
• Why LIMIT needs ORDER BY — without sorting first, "the first 2 rows" isn't well defined.

The database's optimizer may actually execute steps in a different physical order for speed, but the results always match this logical order.