JOINs: Connect the Tables

Why tables point at each other

The orders table stores customer_id = 3, not "Mei Lin". That is on purpose. If Mei changes her name we update one row, not twenty. A JOIN follows those id links and glues rows from two tables together.

Example: Orders with customer names

SELECT orders.id, customers.name, orders.quantity FROM orders JOIN customers ON orders.customer_id = customers.id;

ON tells SQL which columns must match. Every order row is paired with the one customer row whose id matches.

Table aliases keep it readable

Give each table a short alias in FROM/JOIN and use it everywhere. o.quantity beats orders.quantity.

Example: Same query, shorter

SELECT o.id, c.name, o.quantity FROM orders o JOIN customers c ON o.customer_id = c.id;

Aliases are mandatory when you join a table to itself; otherwise they are just polite.

Join three tables

Chain JOINs. Each one adds a table and an ON condition. Now we can compute real revenue: quantity × price.

Example: Who ordered what, and what it cost

SELECT c.name AS customer, p.name AS pizza, o.quantity * p.price AS total FROM orders o JOIN customers c ON o.customer_id = c.id JOIN pizzas p ON o.pizza_id = p.id;

This is the single most common shape of query in any business app.

LEFT JOIN: keep the lonely rows

A plain (INNER) JOIN drops rows with no match. Felix has never ordered, so he vanishes. LEFT JOIN keeps every row from the left table and fills the right side with NULLs when nothing matches.

Example: Every customer, even those with no orders

SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id ORDER BY c.name;

Scroll to Felix Wagner. His order_id is NULL. Change LEFT JOIN to JOIN and he disappears.

JOIN + GROUP BY = reports

Combine everything: join for names, group for totals.

Example: Revenue per pizza

SELECT p.name, SUM(o.quantity * p.price) AS revenue FROM orders o JOIN pizzas p ON o.pizza_id = p.id GROUP BY p.name ORDER BY revenue DESC;

Truffle Shuffle wins because gourmet pizzas are expensive and people keep ordering two.

Practice exercises

1. Who made it?

   Show each pizza name next to its chef's name. Return columns pizza and chef (use aliases).

2. Order receipts

   For every order, show the customer name, the pizza name and the quantity. Columns: customer, pizza, quantity.

3. Never ordered

   Find the name of every customer who has never placed an order. (LEFT JOIN + IS NULL.)

4. Spend per customer

   For each customer who has ordered, show their name and total spend (quantity * price) as spent. Sort by spent descending.

5. Chef leaderboard

   How many pizzas has each chef sold (sum of order quantities)? Return chef name and pizzas_sold, highest first. Chefs with zero sales should still appear with 0.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the JOINs quiz: 6 timed questions.