The Order of a Query's Parts

You now know all the parts of a query. You write them in this order, and you can leave out any of them except SELECT:

SELECT …
FROM …
WHERE …
GROUP BY …
HAVING …
ORDER BY …
LIMIT …;

The database, however, doesn't run them in the order you write them, but in the order in which it needs them:

1. FROM – takes the rows of the table.
2. WHERE – keeps the rows that meet its condition.
3. GROUP BY – puts the rows into groups, and the aggregate functions compute their values for each group.
4. HAVING – keeps the groups that meet its condition.
5. SELECT – computes the columns of the result and names them with AS (and DISTINCT removes duplicate rows).
6. ORDER BY – sorts the rows of the result.
7. LIMIT – keeps only the first rows (after skipping OFFSET rows).

This order explains much of what you've seen in this lesson. WHERE runs before GROUP BY, so it can't use aggregate functions: there are no groups yet. HAVING runs after GROUP BY, so it can. And ORDER BY runs after SELECT, so it already knows the names that you gave the columns: that's why ORDER BY game_count DESC works.

By the same logic, WHERE, GROUP BY and HAVING can't use the aliases, because they run before SELECT has made them. In most databases, such as PostgreSQL, MySQL, SQL Server or Oracle, an alias in WHERE is an error, and some of them don't allow it in GROUP BY or HAVING either. SQLite is lenient: when it finds a name that isn't a column of the table, it looks among the aliases, so this works in SQLite, even in WHERE:

SELECT title, price * 2 AS two_copies
FROM games
WHERE two_copies > 100;

Don't rely on it, though. An alias that has the name of a real column means the column; an alias of an aggregate function can't be used in WHERE even in SQLite, because there are no groups yet; and in another database, such a query stops working. In WHERE, GROUP BY and HAVING, write the expression itself (WHERE price * 2 > 100, HAVING COUNT(*) >= 3), and use aliases in ORDER BY.

In the second query of this exercise, you'll finally leave out the refunded orders. Whether an order was refunded is written in orders, but the items are in order_items, and to use both tables in one query, you need to join them, which is what the next lesson is about. Until then, you can use the IDs of the refunded orders: SELECT id FROM orders WHERE status = 'refunded'; finds that they're 7 and 18, and WHERE order_id NOT IN (7, 18) then leaves their items out. It's a stopgap, though: a list of IDs written by hand is out of date as soon as another order is refunded, whereas a query with a join finds the refunded orders by itself.