INNER JOIN and the ON Clause

An inner join keeps only the pairs that satisfy ON, which usually follows a foreign key. This query prices each paid order and names its customer:

Two inner joins along foreign keys, then aggregationSQL

SELECT o.id, c.name, SUM(i.quantity * i.unit_price) AS total
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
INNER JOIN order_items AS i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id
ORDER BY total DESC;

Output

+----+--------------+-------+
| id | name         | total |
+----+--------------+-------+
|  9 | Ben Carter   | 53.99 |
|  7 | Farah Rahman | 50.00 |
|  3 | Chen Wei     | 49.00 |
+----+--------------+-------+
3 rows in set (0.002 sec)

c.name needs no grouping: o.id fixes o.customer_id, and the ON equality fixes c.id. An order without items would vanish, which is what makes the join inner.

Keep join logic in ON and filters in WHERE. ON takes any expression, such as ON p.price BETWEEN b.low AND b.high. USING (col) abbreviates ON a.col = b.col. Avoid NATURAL JOIN, which joins on every shared column name and changes silently when a column is added.

MENU

INNER JOIN and the ON Clause