Free lesson
Joining tables
Real data lives in many tables. Learn to stitch them together, and to notice when rows go missing.
In this lesson you will
- Why data is split across tables
- Combine two tables with JOIN
- Find rows that have lost their match
Words you'll meet
- JOIN
- Combine two tables by matching a column in one to a column in the other.
- LEFT JOIN
- A join that keeps every row of the first table, even without a match.
- Orphan
- A row that points at something that does not exist.
The lesson, step by step
Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.
Step 1 of 7
One fact, one place
So far everything lived in one table. Real systems split data up: customers in one table, orders in another.
Each order stores a
customer_id, a number pointing at one customer, instead of copying the customer's name and city onto every order. Each fact is written down in exactly one place.Your turn: Ada moves to Denver. With the data split like this, what needs changing?
Step 2 of 7
Follow the pointer
The catch: an order on its own no longer says who placed it. Order 5 says
customer_id = 4. To find out who that is, you look up row 4 incustomers.Your turn: Order 5 was placed by customer_id 4. Click that customer's name.
Step 3 of 7
JOIN
A JOIN puts two tables side by side, pairing rows that match:
SELECT orders.order_id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.customer_id
ONis the matching rule. Because both tables have a column calledcustomer_id, you say which one you mean withtable.column.Writing table names in full gets long. You can give a table a short nickname, called an alias:
FROM orders o JOIN customers c ON o.customer_id = c.customer_id. Theno.itemmeansorders.item.Your turn: Show each order's order_id, item and the customer's name.
Step 4 of 7
A missing order
Count carefully.
ordershas 7 rows, but your join returned 6.A plain
JOIN(also called an inner join) only keeps rows that find a match on both sides. Here is order 7.Your turn: Why did order 7 disappear from the join?
Step 5 of 7
LEFT JOIN
When you must not lose rows, use a LEFT JOIN. It keeps every row from the table written first (the left one), matched or not:
SELECT orders.order_id, customers.name FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id
Where there is no match, the columns from
customerscome back asNULL.Your turn: Show the order_id and the customer's name for every order, including order 7.
Step 6 of 7
Hunt the orphans
A row that points at something that does not exist is called an orphan. Orphans mean something upstream went wrong, so data engineers hunt for them on purpose.
The trick: LEFT JOIN, then keep only the rows where the match came back empty.
... WHERE customers.customer_id IS NULL
Your turn: Show the order_id of every order whose customer is missing.
Step 7 of 7
Challenge
Recap. A JOIN brings each customer's city onto their orders.
GROUP BYmakes one pile per city, andSUMadds each pile up. A JOIN goes between FROM and GROUP BY.The owner wants revenue by city, but the city now lives in a different table from the money.
Your turn: Show each city and its revenue (sum of quantity times price), highest first.
Reading is the easy part.
Every step above has an exercise. Do it for real, free, no account needed.
Start the lesson