INNER JOIN, Step by Step

lesson 5-2 · ~13 min · 13/29

The most important query in SQL

Splitting data into two tables created a new problem: SELECT * FROM orders shows customer_id = 1, not Ana. A JOIN stitches the tables back together at query time:

SELECT customers.name, orders.item, orders.price
FROM orders
JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.id;

Read the middle line slowly, it is the whole trick: for each row of orders, find the customers row whose id equals this row's customer_id, and glue the two rows together.

The ON condition says how rows match.
Because both tables have columns in play, you prefix each column with its table name: customers.name, orders.item.
JOIN by itself means INNER JOIN: rows that find no partner are dropped (that detail matters in unit 6).

Watch the figure below: the highlight walks through each order and jumps to the matching customer.

INNER JOIN walks the orders table row by row. For each order, it finds the customers row whose id equals the order's customer_id and glues the two rows into one result row.

Code exercise · sql

Run it. Five orders in, five joined rows out, each showing the customer's actual name next to what they bought.

Quiz

In the join above, why does Ana appear twice in the output?

Code exercise · sql

Your turn. For every order, show the item and the CITY it ships to, in order id sequence. You need a column from each table.