Skip to content
Advertisement

difference between ‘where’ null and ‘on’ in a left join

Could someone explain to me why

yields less results than

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

push table

With this data, the query with the where clause returns only

while the one with the on condition returns all wanted rows

Advertisement

Answer

The difference is when there is a match but on another row. This is best shown with a small example.

Consider:

t1:

t2:

Then the left join version returns all three rows in t1:

The filtering in the where clause version:

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement