Joins with WHERE and ORDER BY

A join combines with everything you know: WHERE, ORDER BY, LIMIT and functions. WHERE and ORDER BY come after the joins, and they can use the columns of all the joined tables. Which games were in refunded orders?

SELECT o.id, o.ordered_at, g.title
FROM order_items AS i
JOIN orders AS o ON i.order_id = o.id
JOIN games AS g ON i.game_id = g.id
WHERE o.status = 'refunded'
ORDER BY o.ordered_at, g.title;

Remember from the previous lesson the order in which the parts of a query run. First comes FROM, now with all its JOINs: they build the joined rows. Then WHERE keeps some of them, and after it come GROUP BY, HAVING, SELECT, ORDER BY and LIMIT. So WHERE and ORDER BY see the columns of every joined table, whether you show them or not. The query above filters by o.status and sorts by o.ordered_at, and it would work even if neither of them were in SELECT.

A join also lets you compare the columns of two tables with each other, which no query on one table can do. Which items were sold for less than the game's price in the catalog?

SELECT i.order_id, g.title, g.price AS list_price, i.price AS paid
FROM order_items AS i
JOIN games AS g ON i.game_id = g.id
WHERE i.price < g.price
ORDER BY i.order_id, g.title;

Just the two games of the Black Friday order. In WHERE, qualified names matter as much as in SELECT: WHERE price > 20 would end with the error ambiguous column name: price.

Typical mistake: WHERE before a JOIN. The order of the parts is fixed: all the JOINs belong to FROM, and WHERE comes after the last of them. Otherwise, you get the error near "JOIN": syntax error.