INNER JOIN, Step by Step

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.

Orders with their customer names

Five orders go in and five joined rows come out, each one showing the customer's actual name next to what they bought.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

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

Output

Ana|coffee|5
Ben|sandwich|8
Ana|bagel|4
Cara|coffee|5
Ben|coffee|5

The ON clause states the rule for pairing rows: a customer row and an order row belong together when the ids agree. Columns are written as table.column because two tables are in play and both happen to have an id, so SELECT id on its own would be ambiguous.

The manual two-step lookup from the previous lesson is gone. One query, one trip to the database, and it works for five orders or five million without changing a character.

Why one customer produces two rows

Ana appears twice because each of her two orders matches her single customer row once, and a join emits one result row per match.

Orders 101 and 103 both carry customer_id = 1, so each pairs with Ana. One customer plus two orders gives two joined rows, with her name repeated in both.

That repetition is normal and expected. The output of a join is a new result table whose row count follows the number of matches, not the number of rows in either input, so it can easily be larger than either original table.

This is also why counting rows after a join needs care. COUNT(*) on this result answers "how many orders" rather than "how many customers", and confusing the two produces inflated numbers in reports.

Pulling the shipping city into the result

Once the join is written, asking for different columns is the only change needed.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

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

Output

coffee|Lima
sandwich|Tokyo
bagel|Lima
coffee|Paris
coffee|Tokyo

The JOIN and ON lines are untouched. item lives in orders and city lives in customers, so the SELECT list simply names one column from each side.

Lima shows up twice, once for each of Ana's orders, which is the same repetition explained above. A joined result is a single flat table, and any column from either side is now available to WHERE, GROUP BY, and ORDER BY as well.