Joining Tables: INNER JOIN

Data is split across tables to avoid repetition. A join combines them back together by matching related columns — usually a foreign key with the primary key it refers to.

Real-world analogy: a stack of receipts shows customer numbers; a customer list maps numbers to names. Joining them is like writing each customer's name onto their receipts.

Syntax

SELECT columns
FROM table_a
JOIN table_b ON table_a.column = table_b.column;

Example

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

Expected output

+----+-------------------+------------+-----------+
| id |       name        | ordered_on |  status   |
+----+-------------------+------------+-----------+
|  1 | Ada Lovelace      | 2025-01-05 | delivered |
|  2 | Ada Lovelace      | 2025-02-14 | delivered |
|  3 | Linus Torvalds    | 2025-02-20 | shipped   |
|  4 | Grace Hopper      | 2025-03-01 | delivered |
|  5 | Grace Hopper      | 2025-03-18 | cancelled |
|  6 | Alan Turing       | 2025-04-02 | pending   |
|  7 | Margaret Hamilton | 2025-04-10 | shipped   |
|  8 | Ada Lovelace      | 2025-04-22 | pending   |
+----+-------------------+------------+-----------+

• JOIN (short for INNER JOIN) pairs each order with the customer whose id matches the order's customer_id.
• ON gives the matching condition.
• Columns are written as table.column, because both tables have an id column — id alone would be ambiguous.

Inner joins keep only matches

An inner join returns only rows that have a match in both tables. Tim Berners-Lee (customer 6) has no orders, so he doesn't appear. If an order referred to a customer ID that didn't exist, that order wouldn't appear either. The next lessons show how to keep unmatched rows.

Joining and filtering

SELECT customers.name, orders.id AS order_id, orders.status
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE customers.city = 'London'
ORDER BY orders.id;

+--------------+----------+-----------+
|     name     | order_id |  status   |
+--------------+----------+-----------+
| Ada Lovelace |        1 | delivered |
| Ada Lovelace |        2 | delivered |
| Alan Turing  |        6 | pending   |
| Ada Lovelace |        8 | pending   |
+--------------+----------+-----------+

Common mistakes

• Forgetting the ON condition (or getting it wrong), which pairs every row with every other row — a cross join that produces far too many rows.
• Ambiguous column names: SELECT id FROM orders JOIN customers ... fails with ambiguous column name: id. Prefix columns with their table.

Practice

List each order item with the product's name: show order_id, the product name, and quantity.

SELECT order_items.order_id, products.name, order_items.quantity
FROM order_items
JOIN products ON products.id = order_items.product_id
ORDER BY order_items.order_id;

+----------+-----------------------------+----------+
| order_id |            name             | quantity |
+----------+-----------------------------+----------+
|        1 | Mechanical Keyboard         |        1 |
|        1 | USB-C Hub                   |        2 |
|        2 | 27-inch Monitor             |        1 |
|        3 | USB-C Hub                   |        1 |
|        3 | Noise-Cancelling Headphones |        1 |
|        4 | 27-inch Monitor             |        2 |
|        4 | Laptop Stand                |        2 |
|        5 | Webcam                      |        1 |
|        6 | Mechanical Keyboard         |        1 |
|        7 | Laptop Stand                |        1 |
|        7 | Noise-Cancelling Headphones |        1 |
|        8 | Webcam                      |        3 |
+----------+-----------------------------+----------+