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.