Read it as: take each order, find every customer whose id equals the order's customer_id, and output one combined row per pair. Order 13 has no matching customer, so it disappears. Chen has no matching order, so Chen disappears too. That is the defining property of an inner join: only rows that found a partner survive, from both sides.

JOIN and INNER JOIN are the same thing in MySQL; the word INNER is optional. The ON clause is a boolean condition evaluated for each candidate pair — usually key equality, but any expression is allowed.

A mental model: filter the Cartesian product

Logically, a join builds every possible pair of rows — the Cartesian product, 4 orders × 3 customers = 12 pairs here — and keeps the pairs where ON is true. The database never actually builds all twelve; it uses indexes, hash tables or sorted merges. But the model gives the right answer every time, including in the strange cases.

The strange cases, read off the grid:

• a row that matches nothing produces no pair;
• a row that matches three rows produces three pairs;
• a NULL key matches nothing, because NULL = 1 is not true — it is unknown, and ON keeps only true.

Aliases and qualified columns

Both tables have an id column. Writing SELECT id FROM orders JOIN customers ON … fails in MySQL with Column 'id' in field list is ambiguous. Qualify every column: o.id, c.id. Table aliases (orders AS o, or just orders o) keep that short. Once a table has an alias, use the alias everywhere in the query — MySQL refuses orders.id when the table was renamed o.

Also alias the output columns. Two columns both called id or name in a result are confusing to read and impossible to tell apart in application code that reads columns by name.

One-to-many: why rows repeat

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

Asha appears twice because she has two orders. This is correct, and it is what makes aggregation after a join work:

SELECT c.name, COUNT(*) AS orders, SUM(o.amount) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC, c.name;

Group by the key (c.id) as well as the name: two different customers can share a name, and grouping by name alone would merge them. The danger comes when a column from the "one" side is summed after the join — summing a customer's credit limit over their order rows counts it once per order. Keep that in mind; the joining-many-tables lesson returns to it.

Joining and filtering

WHERE runs after the join and filters the combined rows, so it may refer to either table:

SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Pune' AND o.amount >= 200
ORDER BY o.id;

For an inner join it makes no difference whether a condition is placed in ON or in WHERE — the rows that survive are the same. Convention puts the relationship (c.id = o.customer_id) in ON and the filters in WHERE. For outer joins the placement changes the answer, which is the first trap of the next lesson.

Other spellings you will meet

-- USING: the key has the same name in both tables
SELECT * FROM orders JOIN shipments USING (order_id);

-- Comma join: the older, implicit form
SELECT o.id, c.name FROM orders o, customers c WHERE c.id = o.customer_id;

-- NATURAL JOIN: joins on every column name the tables share
SELECT * FROM orders NATURAL JOIN customers;   -- avoid

USING is fine when the names match; it also prints the shared column once. The comma join is equivalent to an inner join, but if you forget the WHERE condition you silently get the Cartesian product — the explicit JOIN … ON makes the missing condition obvious. NATURAL JOIN is a trap: here both tables have id, so it would join orders.id = customers.id, which is meaningless, and adding a column later can change the result without any error.

Conditions other than equality

Most joins are equi-joins, but the ON clause accepts any condition. A price band lookup is a classic non-equi join:

SELECT p.name, b.label
FROM products p
JOIN price_bands b ON p.price BETWEEN b.low AND b.high
ORDER BY p.name;

Each product finds the band whose range contains its price. If the bands overlap, a product matches two bands and appears twice — the same row-multiplication rule as before.

Pitfalls

• Selecting an unqualified column that exists in both tables (ambiguous column error).
• Expecting unmatched rows to appear — an inner join drops them on both sides.
• Grouping by a non-unique column such as a name instead of the key.
• Summing a parent-side value after a one-to-many join and counting it repeatedly.
• NATURAL JOIN or a comma join without its condition.

Key takeaways

• JOIN … ON pairs rows whose condition is true; rows without a partner vanish from both sides.
• Think "Cartesian product, then filter by ON" to predict any result.
• NULL keys never match, because NULL = x is unknown.
• One-to-many joins repeat the parent row once per child — correct, and the basis of join + GROUP BY.
• Alias tables, qualify every column, alias output columns, and prefer ON to NATURAL.