Free lesson

Joining tables

Real data lives in many tables. Learn to stitch them together, and to notice when rows go missing.

10 min7 steps

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.
Start the lesson

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.

  1. 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?

  2. 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 in customers.

    Your turn: Order 5 was placed by customer_id 4. Click that customer's name.

  3. 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

    ON is the matching rule. Because both tables have a column called customer_id, you say which one you mean with table.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. Then o.item means orders.item.

    Your turn: Show each order's order_id, item and the customer's name.

  4. Step 4 of 7

    A missing order

    Count carefully. orders has 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?

  5. 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 customers come back as NULL.

    Your turn: Show the order_id and the customer's name for every order, including order 7.

  6. 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.

  7. Step 7 of 7

    Challenge

    Recap. A JOIN brings each customer's city onto their orders. GROUP BY makes one pile per city, and SUM adds 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