Home›SQL›MySQL – INNER JOIN

MySQL – INNER JOIN

July 20, 20244 min readUpdated 8/25/2026

A relational database splits information across tables on purpose. The pizza schema keeps products in product and their per-size prices in product_size, so changing a price touches one row instead of many. A join is how you put those pieces back together in one result.

INNER JOIN is the one you will use most: it returns rows that have a match on both sides. JOIN with no qualifier means exactly the same thing — INNER is optional and usually left out.

Joining two tables

SELECT p.name, ps.size, ps.price
FROM   product p
JOIN   product_size ps ON ps.product_id = p.id
WHERE  p.name = 'Hawaiian Pizza'
ORDER  BY ps.price;
+----------------+--------+-------+
| name           | size   | price |
+----------------+--------+-------+
| Hawaiian Pizza | SMALL  | 12.49 |
| Hawaiian Pizza | MEDIUM | 15.49 |
| Hawaiian Pizza | LARGE  | 18.49 |
+----------------+--------+-------+

One product row, three price rows, three rows out. The product's name is repeated on each, because a join produces a flat table — there is no nesting in a result set.

Two pieces of syntax are doing the work:

  • The alias. product p lets you write p.name. Once two tables are involved, a bare name is ambiguous and MySQL will say so.
  • The ON clause. It says which rows belong together. Here it is the foreign key — product_size.product_id points at product.id — which is the common case but not a rule. You can join on anything that compares.

How to read it

Conceptually MySQL pairs every row on the left with every row on the right, then keeps the pairs where ON is true. That is a useful mental model and a terrible execution plan, so the optimizer does something far cleverer — usually looking each match up through an index. The result is the same; only the speed differs. See EXPLAIN for what it actually chose.

More than two tables

Joins chain. An order has line items, and a line item has toppings — three levels in the pizza schema:

SELECT o.id AS order_id, i.product_name, t.topping_name, t.price
FROM   customer_order o
JOIN   order_item i        ON i.order_id = o.id
JOIN   order_item_topping t ON t.order_item_id = i.id
ORDER  BY o.id, i.id, t.id
LIMIT  6;
+----------+---------------------+---------------+-------+
| order_id | product_name        | topping_name  | price |
+----------+---------------------+---------------+-------+
|        3 | Meat Lovers Pizza   | Extra Cheese  |  1.75 |
|        7 | Veggie Lovers Pizza | Mushrooms     |  1.00 |
|        7 | Veggie Lovers Pizza | Green Peppers |  1.00 |
|        8 | Pepperoni Pizza     | Extra Cheese  |  1.75 |
|        8 | Pepperoni Pizza     | Bacon         |  1.75 |
|       13 | Pepperoni Pizza     | Mushrooms     |  1.00 |
+----------+---------------------+---------------+-------+

Each JOIN adds a table and each ON attaches it to something already in the query. Notice that the result starts at order 3 — orders 1 and 2 have line items but no toppings on them, and an inner join drops anything without a match at every level. Keeping them is LEFT JOIN's job.

Rows in, rows out

A join changes the row count, and forgetting that is the most common way to get a wrong total:

SELECT COUNT(*) AS matched_rows
FROM   customer_order o
JOIN   order_item i ON i.order_id = o.id;
+--------------+
| matched_rows |
+--------------+
|           27 |
+--------------+

There are 18 orders, and 27 rows come back — one per line item. So SUM(o.total) over this join is not the revenue: every order's total is counted once per line item it happens to have. This is the classic fan-out bug, and it is silent. Aggregate the detail table, or aggregate in a subquery, but do not sum a column from the "one" side across a join to the "many" side. See GROUP BY.

Joining on something other than a key

ON takes any condition, and it can have several:

SELECT o.id, i.product_name, i.unit_price
FROM   customer_order o
JOIN   order_item i ON i.order_id = o.id AND i.unit_price > 15
ORDER  BY o.id
LIMIT  3;

For an inner join, putting a filter in ON and putting it in WHERE give the same rows. For an outer join they emphatically do not — that difference is the whole point of the LEFT JOIN lesson, so it is worth getting the habit right here: ON describes the relationship, WHERE filters the result.

The comma join, and why not to use it

-- the old syntax: the relationship is buried in WHERE
SELECT p.name, ps.price FROM product p, product_size ps WHERE ps.product_id = p.id;

-- the same query, saying what it means
SELECT p.name, ps.price FROM product p JOIN product_size ps ON ps.product_id = p.id;

Both work. The first hides the relationship among the filters, cannot express an outer join at all, and — if you forget the WHERE — silently returns every row times every row. That is a CROSS JOIN, and it should be something you ask for rather than something you get.

What to remember

  • JOIN and INNER JOIN are the same thing: rows matching on both sides.
  • Alias your tables; qualify your columns.
  • A join to a "many" table multiplies rows — do not SUM the "one" side across it.
  • ON describes the relationship; WHERE filters the result.

Next: LEFT JOIN, for when the match may not be there.