Could someone explain to me why
select "talent".* from "talent"
left join "push" on "push"."talentId" = "talent"."id"
where ("push"."offerId" = '403' or "push"."offerId" is null)
yields less results than
select "talent".* from "talent"
left join "push" on "push"."talentId" = "talent"."id" and "push"."offerId" in ('403')
The way I see it, it should boil down to the same result, but it doesn’t, and I’m not sure what I miss to get it.
first one does not contain rows that have no entry in the push table.
I’d expect them to be caught by the or "push"."offerId" is null.
EDIT:
here is an example:
talent table
+----+------+ | id | name | +----+------+ | 1 | John | | 2 | Bob | | 3 | Jack | +----+------+
push table
+----+----------+---------+ | id | talentId | offerId | +----+----------+---------+ | 1 | 1 | 403 | | 2 | 1 | 42 | | 3 | 2 | 123 | | 3 | 2 | 456 | +----+----------+---------+
With this data, the query with the where clause returns only
+----+------+---------+ | id | name | offerId | +----+------+---------+ | 1 | John | 403 | +----+------+---------+
while the one with the on condition returns all wanted rows
+----+------+---------+ | id | name | offerId | +----+------+---------+ | 1 | John | 403 | | 2 | Bob | null | | 3 | Jack | null | +----+------+---------+
Advertisement
Answer
The difference is when there is a match but on another row. This is best shown with a small example.
Consider:
t1:
x y 1 abc 1 def 2 xyz
t2:
x y 1 def
Then the left join version returns all three rows in t1:
select *
from t1 left join
t2
on t1.x = t2.x and t1.y = t2.y;
The filtering in the where clause version:
select *
from t1 left join
t2
on t1.x = t2.x
where t2.y = 'abc' or t2.y is null;
returns only one rows. The row that is returned is 1/abc. x = 2 matches in t2. So, t2.y is not null. And it is not 'abc' either. So it is filtered out.
Here is a db<>fiddle.