Joining two tables

Lesson 11 of 21 · Part 3 · More than one table · about 15 minutes

The orders table records which customer placed each order, but only by the customer's number:

1SELECT * FROM orders ORDER BY id;

Order 4 was placed by the customer whose customer_id is 4. What is that customer's name, and which city do they live in? For that you have to look in the customers table. This lesson is about using two tables in one query.

Why several tables?

Couldn't each order just hold the customer's name and city? It could, but customer 1 has placed 3 orders, so her name and city would be written 3 times. If she moves one day, all 3 places have to change; miss one and the data contradicts itself.

So databases usually store it like this: a customer's details are written once, in the customers table, and an order holds only the customer's number. When you need both, you join the two tables by that number.

JOIN … ON

FROM table1 JOIN table2 ON condition pairs up the rows of the two tables, and ON says how to pair them. Here, the order's customer_id equals the customer's id:

1SELECT orders.id, orders.order_date, customers.name2FROM orders3JOIN customers ON orders.customer_id = customers.id4ORDER BY orders.id;

A column name both tables have (such as id) is written table.column, to say which table it comes from.

Press Step and look at the JOIN step: each order takes its customer_id to the customers table, finds the row with the same id, and the two are put together into one row. 12 orders make 12 rows.

JOIN can also be written INNER JOIN; it means exactly the same.

Table aliases

Writing the table names out every time gets long. Give each table a short name in FROM and JOIN, and use the short name from then on:

1SELECT o.id, o.order_date, c.name, c.city2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4ORDER BY o.id;

orders AS o means "the orders table, called o from here on". The AS can be left out: orders o means the same.

Columns with the same name

A column that only one table has, such as order_date or name, can go without the table name. A column both tables have can't:

1SELECT id, name, order_date2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id;

ERROR 1052 (23000): Column 'id' in field list is ambiguous

ERROR 1052: column id is "ambiguous". The orders table has an id, and so does the customers table, and the database doesn't know which one you mean. Write o.id or c.id.

Tip: In a join, put the table alias on every column (o.id, c.name), even where leaving it out would work. Anyone reading can see at a glance where each column comes from.

Unmatched rows are gone

Now the other way round, from the customers to their orders:

1SELECT c.id, c.name, o.id AS order_id2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4ORDER BY c.id, o.id;

Press Step again and watch the pairing: customer 1 matches 3 orders and becomes 3 rows; customers 6, 9 and 10 have no orders at all. No match, no row: they aren't in the result at all.

JOIN keeps only rows that found a partner on both sides. To keep the customers who never ordered as well, you need lesson 12's LEFT JOIN.

1SELECT p.name, oi.order_id2FROM products AS p3JOIN order_items AS oi ON oi.product_id = p.id4ORDER BY oi.order_id, p.id;

JOIN with WHERE

A joined result is like any table: WHERE and ORDER BY work on it as usual. Which orders did customers in London place?

1SELECT o.id, o.order_date, c.name2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4WHERE c.city = 'London'5ORDER BY o.id;

city is in the customers table and order_date in the orders table; after the join, each row has both, so WHERE can use a column from either table.

What's in an order

order_items has only product numbers. Join the products table and you can see what order 3 contains:

1SELECT p.name, oi.quantity, oi.unit_price2FROM order_items AS oi3JOIN products AS p ON oi.product_id = p.id4WHERE oi.order_id = 35ORDER BY p.id;

Joining three tables at once (orders → order lines → products) comes in lesson 13.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT id, order_date, customer_id2FROM orders3WHERE status = 'pending';

1SELECT order_id, product_id, quantity FROM order_items;

1SELECT id, name, order_date2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4WHERE city = 'Tokyo'5ORDER BY id;

What you learned

• Data is split across tables so that each fact is written once; the tables are linked by id numbers.
• FROM table1 JOIN table2 ON condition puts matching rows together into one; JOIN is the same as INNER JOIN.
• Give tables aliases (orders AS o) and write columns as o.id; a column both tables have, without a table name, gives ERROR 1052.
• Rows with no match don't appear in the result; to keep them, use lesson 12's LEFT JOIN.
• A joined result works with WHERE and ORDER BY as usual.