Joining Two Tables

A join puts the rows of two tables together. Here's the question from the previous exercise, who placed which order, answered for all the orders at once:

SELECT orders.id, ordered_at, name
FROM orders
JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.id;

JOIN customers adds the table customers to the query, and the condition after ON says which rows belong together: an order goes with the customer whose id is the order's customer_id. It's the foreign key from the previous exercise, only now the database follows it instead of you. orders.customer_id is the column customer_id of the table orders: the name of the table, a dot, and the name of the column. Both tables have a column id, so you have to say whose id you mean, in ON and in SELECT too. You'll learn more about that in the next exercise.

How does the database find the pairs? You can think of it like this: it goes through the rows of orders one by one, and for each of them, it looks for the rows of customers for which the condition holds. Each pair it finds becomes one row of the result, with the columns of both tables side by side, and SELECT then picks which of them to show. Order 1 has the customer_id 1, the condition holds for Emma Clark's row, and the result gets a row with order 1 and Emma Clark. Order 4 has the customer_id 1 too, so Emma Clark is in the result again, next to order 4. That's how it should be: each customer is in the result as many times as they have orders.

And what about a customer who has no order? Henry Scott has never ordered anything: no order has the customer_id 12, his id. His row has nothing to pair up with, so it isn't in the result at all. JOIN keeps only the rows that found a match; the others disappear. For a list of orders, that's just what you want. When you need to keep them, there's another kind of join, which you'll learn later in this lesson.

You'll also see INNER JOIN: it's the same as JOIN; INNER is just the full name of this kind of join. The order of the tables doesn't change which pairs a JOIN finds: FROM customers JOIN orders ON … gives the same rows, and so does writing the two sides of = the other way round.

Typical mistake: a condition that connects the wrong columns. ON orders.id = customers.id runs without an error, but it pairs order 1 with customer 1, order 2 with customer 2, and so on, and that means nothing. The condition connects a foreign key with the primary key it points at: here, orders.customer_id with customers.id.