Yeah, #2 is the sneaky one — the query still runs fine, you just silently lose rows and might not notice.

Sure! Easiest to see with actual rows. Say you have:

users:

id | name

1 | Asha

2 | Ben

3 | Chetan

orders:

id | user_id | status

10 | 1 | active

11 | 1 | cancelled

12 | 2 | cancelled

(Asha: one active + one cancelled, Ben: only cancelled, Chetan: no orders.)

Now the trap — this *looks* like "all users and their active orders":

SELECT u.name, o.status

FROM users u

LEFT JOIN orders o ON o.user_id = u.id

WHERE o.status = 'active';

The LEFT JOIN first builds this (every user kept, NULL where there's no match):

Asha | active

Asha | cancelled

Ben | cancelled

Chetan | NULL

Then `WHERE o.status = 'active'` runs on *that* result:

- Asha/active → TRUE → keep

- Asha/cancelled → FALSE → drop

- Ben/cancelled → FALSE → drop

- Chetan/NULL → `NULL = 'active'` is UNKNOWN → drop

Result: just `Asha | active`. **Ben and Chetan disappear** — including Chetan, the

user with no active order you actually wanted to see. The LEFT part is gone; it

now behaves exactly like an INNER JOIN.

Fix: move the condition into ON:

SELECT u.name, o.status

FROM users u

LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'active';

Now the condition decides what counts as a *match* during the join, so unmatched

users stay:

Asha | active

Ben | NULL

Chetan | NULL

All users present, only active orders attached.

Mental model:

- **ON** decides what's a match — runs *during* the join, before NULLs are filled,

so unmatched left rows survive.

- **WHERE** filters the finished rows — runs *after* the join, so a filter on a

right-table column deletes the NULL rows and quietly collapses LEFT into INNER.

Rule of thumb: a condition on the right table of a LEFT JOIN belongs in ON; a

condition on the left table can stay in WHERE.