Databasesmedium

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

Answer

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table and fills missing matches from the right side with NULLs.

SELECT u.id, p.id
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;

Advanced answer

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).