Explain INNER, LEFT, and RIGHT JOIN in MySQL with examples.

Question

IT Interview Practice · Accepted Answer

A JOIN combines rows from two tables by matching a condition, usually a foreign key. The join *type* decides what happens to rows that have **no match** on the other side.

## The three you need

- **INNER JOIN** — keep only rows that match on both sides.
- **LEFT JOIN** — keep every row from the left table; unmatched right columns come back `NULL`.
- **RIGHT JOIN** — mirror image: keep every right row. Rarely used; people flip the tables and write LEFT instead.

```sql
-- customers(id, name)   orders(id, customer_id, total)

-- INNER: only customers who have at least one order
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT: every customer, even those with zero orders (total = NULL)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Classic use: find customers with NO orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- the LEFT JOIN's NULL side reveals the gap
```

## Watch out

- Filtering the right table in `WHERE` (e.g. `WHERE o.total > 100`) silently turns a LEFT JOIN back into an INNER JOIN, because `NULL > 100` is false. Put such conditions in the `ON` clause if you want to keep unmatched rows.
- Always join on **indexed** columns (the foreign key) or the join degrades to nested scans.

## Why it matters

Most real queries span multiple tables, and the wrong join type quietly drops or duplicates rows — a bug that returns *plausible* results. Knowing that LEFT JOIN + `IS NULL` finds "missing" rows, and that a `WHERE` on the null side cancels the LEFT, prevents a whole class of reporting errors.

The three you need

Watch out

Why it matters

Related questions