Lesson 5 of 8 · 6 min
Joining tables
INNER JOIN, LEFT JOIN, joining three or more tables, self joins, and the mistakes that quietly multiply or drop rows.
On this page
Data lives in separate tables, so most useful questions need more than one. A join combines rows from two tables where a condition matches, usually a foreign key equal to a primary key.
INNER JOIN
"Show each order with the customer's name":
SELECT o.id, o.ordered_at, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;
| id | ordered_at | name |
|---|---|---|
| 101 | 2026-11-01 | Asha Rao |
| 102 | 2026-11-03 | Neha Gupta |
| 103 | 2026-11-05 | Asha Rao |
| 104 | 2026-11-06 | Imran Shaikh |
JOIN means INNER JOIN: only rows with a match on both sides appear. Rahul has no orders, so he isn't in the result.
Table aliases (o, c) keep queries short, and prefixing every column with its table makes it clear where each value comes from.
LEFT JOIN
"Show every customer, with their orders if they have any":
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.name;
| name | order_id | status |
|---|---|---|
| Asha Rao | 101 | shipped |
| Asha Rao | 103 | pending |
| Imran Shaikh | 104 | cancelled |
| Neha Gupta | 102 | pending |
| Rahul Mehta | NULL | NULL |
A LEFT JOIN keeps every row from the left table. Where there is no match, the right side's columns are NULL.
That gives an easy way to find rows without a match:
-- Customers who have never ordered
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;
RIGHT JOIN is the mirror image; you can always rewrite it as a LEFT JOIN by swapping the tables, which is easier to read. FULL OUTER JOIN keeps unmatched rows from both sides (not available in MySQL).
Joining more tables
"What did each order contain?" needs four tables:
SELECT
o.id AS order_id,
c.name AS customer,
p.name AS product,
oi.quantity,
p.price * oi.quantity AS line_total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
ORDER BY o.id;
Each JOIN adds one table and one condition. Read it top to bottom: start from orders, attach the customer, then the items, then each item's product.
Filter in ON or in WHERE?
With an inner join, it doesn't matter. With a LEFT JOIN, it does:
-- Every customer, with only their pending orders (others show NULL)
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'pending';
-- Only customers who have a pending order (the WHERE removes the NULL rows)
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'pending';
Self join
A table can join to itself. With an employees table that has a manager_id column:
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;
Mistakes to watch for
- A missing or wrong
ONcondition multiplies rows: every row on the left matches every row on the right. If a result is suspiciously large, check the join conditions first. - Joining two "many" tables to the same parent (for example, orders and reviews to customers) multiplies rows too. Aggregate each one separately first, then join the totals.
- Comparing the wrong columns, such as
o.id = c.id. The query runs and returns nonsense. Name foreign keys clearly (customer_id) so the right condition is obvious.
Check yourself
- List every product with the number of the order it was in. Include products that were never ordered.
- Which customers have ordered a product in the Accessories category? Show each name once.
- Find products that have never been ordered.
More from Databases and SQL
- Lesson 7 · 5 minChanging data and transactionsINSERT, UPDATE and DELETE without accidents, upserts, transactions and the ACID guarantees, and how to keep SQL injection out of your application.
- Lesson 8 · 6 minIndexes and schema designHow indexes make queries fast and what they cost, reading a query plan, normalisation in plain words, and a checklist for designing tables.
- Lesson 1 · 6 minWhat is a database?Tables, rows and columns, primary and foreign keys, relational versus NoSQL databases, and what SQL actually is.