INNER JOIN vs LEFT JOIN — what’s the difference?

Deep dive

Joins combine rows from two tables based on a join condition.

• `INNER JOIN`: keep only pairs that match the join condition.
• `LEFT JOIN`: keep all rows from the left table; if there’s no match on the right, right-side columns are `NULL`.

Example

Customers with orders (INNER):

SELECT c.id, o.id AS order_id
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Customers even without orders (LEFT):

SELECT c.id, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Common pitfall (WHERE turns LEFT into INNER)

If you put a condition on the right table in `WHERE`, you can filter out the `NULL` rows and effectively lose the left-join behavior:

-- This removes customers with no orders
... LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID';

To keep left rows, move conditions into the `ON` clause:

... LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'PAID';

Practical notes

• `RIGHT JOIN` is symmetric (swap sides).
• `FULL OUTER JOIN` keeps rows from both sides (not always available in every DB).