Joining more tables

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

To see what an order bought, you need three tables: orders says who ordered and when, order_items says which products each order bought, how many and at what price, and only products has the products' names. This lesson joins several tables together, then groups the joined result.

Three tables

You can write one JOIN after another. This query shows the products in each order of customer 1:

1SELECT o.id AS order_id, o.order_date, p.name, oi.quantity2FROM orders AS o3JOIN order_items AS oi ON oi.order_id = o.id4JOIN products AS p ON p.id = oi.product_id5WHERE o.customer_id = 16ORDER BY o.id, p.id;

Read it from the top: first orders; the first JOIN pairs each order with its items, one row per item; the second JOIN pairs each item with its product. Every JOIN takes "the result so far" and matches it against the next table, and its ON says how to match at that step.

Press Step and watch the two JOINs add their columns one after the other.

Four tables

Join customers as well and you get the customers' names. Here is what customers in London bought; amount is the money spent on that row:

1SELECT c.name, o.id AS order_id, p.name AS product,2    oi.quantity * oi.unit_price AS amount3FROM customers AS c4JOIN orders AS o ON o.customer_id = c.id5JOIN order_items AS oi ON oi.order_id = o.id6JOIN products AS p ON p.id = oi.product_id7WHERE c.city = 'London'8ORDER BY c.id, o.id, p.id;

c.name and p.name are both called name, so the product's name gets the alias product. The more tables, the more columns share a name; put the table alias in front of every column and nothing gets mixed up.

JOIN first, then group

Now work out how much each customer has spent in total. This is where most mistakes happen, so take it slowly.

Don't group yet; just look at the rows after the JOINs. Here is customer 1, Ana Silva, alone:

1SELECT c.name, o.id AS order_id, p.name AS product,2    oi.quantity * oi.unit_price AS amount3FROM customers AS c4JOIN orders AS o ON o.customer_id = c.id5JOIN order_items AS oi ON oi.order_id = o.id6JOIN products AS p ON p.id = oi.product_id7WHERE c.id = 18ORDER BY o.id, p.id;

Ana has 3 orders, but there are 6 rows. After the JOINs, one row is one order item: not one order, and not one customer. What she spent is the sum of these 6 amounts.

Now drop the WHERE and group by customer:

1SELECT c.name, SUM(oi.quantity * oi.unit_price) AS spent2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY spent DESC;

Press Step and go through it:

1. FROM and the two JOINs: 18 rows, each an order item, with its customer and order in front.
2. GROUP BY: those 18 rows go into groups by customer. Ana's group is the 6 rows above.
3. SELECT: each group gets one SUM and becomes one row.

GROUP BY groups the rows after the JOIN, not the rows of the customers table as it was. This query doesn't need product names, so it doesn't join products. GROUP BY includes c.id because two customers could share a name. Customers who never ordered aren't in the result; to include them, use LEFT JOIN from the last lesson.

The order-counting trap

To count each customer's orders in the same query, most people write this:

1SELECT c.name, COUNT(*) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5WHERE c.id = 16GROUP BY c.id, c.name;

To count orders, count "how many different order numbers" with COUNT(DISTINCT o.id), from lesson 7. DISTINCT inside the parentheses removes repeated values before counting. Here are the two side by side:

1SELECT c.name, COUNT(*) AS item_rows, COUNT(DISTINCT o.id) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY c.id;

Tip: Before you write a JOIN with GROUP BY, run it once without grouping and ask "what is one row now?" If a row is an order item, COUNT(*) counts items, not orders.

Joining a table to itself

In the employees table, manager_id is the employee number of that person's manager, and the manager is in the same table. To put each person and their manager's name on one row, join employees to itself. The table is used twice, so give it two different aliases: e for the employee, m for the manager:

1SELECT e.name, e.title, m.name AS manager2FROM employees AS e3JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

Only 5 rows: employee 1, the manager Grace Lee, has no manager; her manager_id is NULL and matches nobody. To keep her in, use LEFT JOIN again:

1SELECT e.name, e.title, m.name AS manager2FROM employees AS e3LEFT JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

Practice

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

1SELECT oi.order_id, oi.quantity * oi.unit_price AS amount2FROM order_items AS oi;

1SELECT c.name, COUNT(*) AS orders, SUM(oi.quantity) AS items2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY c.id;

1SELECT e.name, m.name AS manager2FROM employees AS e3JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

What you learned

• You can write one JOIN after another; each matches the result so far against the next table.
• With many tables, put the table alias in front of column names, and alias columns that share a name.
• JOIN with GROUP BY: the JOIN comes first, then the joined rows are grouped. Run it once ungrouped to see what one row is.
• When a row is an order item, COUNT(*) counts items; count orders with COUNT(DISTINCT o.id).
• A table can be joined to itself with two different aliases; to keep rows with no match, use LEFT JOIN.