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
  1. INNER JOIN
  2. LEFT JOIN
  3. Joining more tables
  4. Filter in ON or in WHERE?
  5. Self join
  6. Mistakes to watch for
  7. Check yourself

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 ON condition 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

  1. List every product with the number of the order it was in. Include products that were never ordered.
  2. Which customers have ordered a product in the Accessories category? Show each name once.
  3. Find products that have never been ordered.

More from Databases and SQL