"in sql / mysql, what is the difference between “on” and “where” in a join statement?" Code Answer

where is a part of the select query as a whole, on is a part of each individual join.

on can only refer to the fields of previously used tables.

when there is no actual match against a record in the left table, left join returns one record from the right table with all fields set to nulls. where clause then evaluates and filter this.

in your query, only the records from gifts without match in 'sentgifts' are returned.

here's the example

gifts

1   teddy bear
2   flowers

sentgifts

1   alice
1   bob

---
select  *
from    gifts g
left join
        sentgifts sg
on      g.giftid = sg.giftid

---

1  teddy bear   1     alice
1  teddy bear   1     bob
2  flowers      null  null    -- no match in sentgifts

---
select  *
from    gifts g
left join
        sentgifts sg
on      g.giftid = sg.giftid
where   sg.giftid is null

---

2  flowers      null  null    -- no match in sentgifts

as you can see, no actual match can leave a null in sentgifts.id, so only the gifts that had not ever been sent are returned.

By daycaster on February 19 2022