Learn/SQL/6. Joining tables
Lesson 6 of 8
Sign in

SQL, lesson 6

Joining tables

Good databases avoid repeating information. Instead of writing a customer's name on every one of their orders, an orders table stores a customer_id, and a separate customers table stores each name once. The id is the link between them. A join follows that link at query time and puts the matching rows side by side.

INNER JOIN

JOIN (short for INNER JOIN) pairs every order with every customer where the ON condition is true. Rows with no partner are left out: here Cy has no orders, so Cy does not appear at all.

When both tables have a column with the same name, such as id, write table.column so SQLite knows which one you mean.

2 exercises to try what you just read.

Exercise 1

Who ordered what

Press Ctrl Enter or Check to test your code.

Exercise 2

Orders per customer

Press Ctrl Enter or Check to test your code.

Aliases keep it short

Give each table a short nickname after its name and use that everywhere:

After the join, the combined rows behave like one wide table. WHERE, GROUP BY, ORDER BY and aggregates all work on it as before.

LEFT JOIN: keep everyone

Sometimes the rows without a partner are the interesting ones: customers who never ordered, products nobody bought. LEFT JOIN keeps every row from the table on the left, even when no row on the right matches. For those rows, the right table's columns are NULL.

Two patterns follow from that:

  • Find the loners: add WHERE o.id IS NULL to keep only the left rows that had no match.
  • Count with zeros: count(o.id) counts only matched rows, so a customer with no orders gets 0 instead of disappearing. count(*) would wrongly give 1, because the row itself still exists.

The table you name first is the "left" one, so choose the order based on which side must be kept complete.

Further reading

  • SQLite: SELECT and join operations

Two tables describe a small bakery. customers has the columns id and name; orders has id, customer_id and item:

Print each order as the customer's name and the item, sorted by name and then by item. Expected output:

Use the same bakery tables. Print every customer's name with the number of orders they placed, including customers with no orders (who get 0). Sort by name. Expected output:

customers                orders
id | name                id | customer_id | total
1  | Ana                 10 | 1           | 30
2  | Bo                  11 | 1           | 12
3  | Cy                  12 | 2           | 45
SELECT customers.name, orders.total
FROM orders
JOIN customers ON customers.id = orders.customer_id;
SELECT c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.total > 20
ORDER BY c.name;
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
-- Cy appears once, with an empty total
customers         orders
id | name         id | customer_id | item
1  | Hana         1  | 2           | Croissant
2  | Omar         2  | 1           | Sourdough
3  | Lucia        3  | 2           | Bagel
4  | Ivan         4  | 3           | Baguette
                  5  | 1           | Muffin
Hana|Muffin
Hana|Sourdough
Lucia|Baguette
Omar|Bagel
Omar|Croissant
Hana|2
Ivan|0
Lucia|1
Omar|2