SQL, lesson 6
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.
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.
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.
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:
WHERE o.id IS NULL to keep only the left rows that had no match.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.
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 | 45SELECT 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 totalcustomers 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 | MuffinHana|Muffin
Hana|Sourdough
Lucia|Baguette
Omar|Bagel
Omar|CroissantHana|2
Ivan|0
Lucia|1
Omar|2