LEFT JOIN with WHERE vs ON filter trap

Filtering on the right-side table inside WHERE quietly turns a "keep everyone" join into a "keep only matches" join, because rows with no match come back as nulls - and WHERE null checks fail, silently dropping them. Moving that filter into the join condition itself keeps all left-side rows intact.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

The LEFT JOIN trap with WHERE on the right side

SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id AND orders.status = 'shipped'

Result:

customer	order_id
Alex Park	1
Jordan Lee
Sam Rivera	3
Tomas Klein

Step by step

After SELECT, list the column names you want, separated by commas.
```
SELECT customers.name, orders.id
```
FROM tells SQL which table to pull the data from.
```
SELECT customers.name, orders.id
FROM customers
```

LEFT JOIN keeps every row from the left table, even when no match exists.

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id AND orders.status = 'shipped';

Practice

Try each one in your head first, then open the answer.

1. List every orders with their shipped shipments only - but keep orders rows even when there are no shipped shipments. Put the status filter in the ON clause, not WHERE.

SELECT orders.*, shipments.* FROM orders LEFT JOIN shipments ON orders.order_id = shipments.order_id AND shipments.status = 'shipped'

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed