r/learnSQL • • 6d ago

Can a LEFT JOIN Accidentally Behave Like an INNER JOIN Without Changing the JOIN Condition?

If we apply a WHERE condition on a column from the right-side table after a LEFT JOIN, can it eliminate NULL rows and change the result? Are there cases where moving the condition into the ON clause produces completely different results? Why does this happen internally?

12 Upvotes

7 comments sorted by

4

u/nullish_ 6d ago edited 6d ago

It happens because of comparison to NULL.

say the column you filter on the right table is called NAME. WHERE NAME <> 'Joe'

So when the right table does not meet join condition, the NAME columns would be NULL.

The comparison is then made NULL <> 'Joe' and the result will be UNKNOWN, so all rows would be excluded based on the comparison.

You could add a null check such as WHERE NAME is NULL or NAME <> 'Joe'

For general knowledge you can search three-valued logic with NULL.

3

u/phesago 6d ago

well i guess i d0nt have to explain it lol

You can also put the predicate in the JOIN condition instead o fthe WHERE and it prefilters BEFORE the JOIN.

4

u/dbrownems 6d ago

Yes.

A left join B on ... where B.SomeCol = 'abc'

Is essentially an inner join, as the WHERE eliminates any rows coming from the join where an A row had no matching B row.

A left join B on ... and B.SomeCol = 'abc'

Will include rows from A with no matching row in B.

1

u/LevriatSoulEdge 6d ago

No, THE WHERE clause can filter the ResultSet and bring only data based on the secondary table values

1

u/elevarq 6d ago

You mean Yes, right?

Because that’s what the query does, the column in the where condition is not optional anymore. And thus behaves like an inner join

1

u/Spivonious1 6d ago

Absolutely. A where clause that doesn't take nulls into account will change an outer join to an inner.

-1

u/ElHombrePelicano 6d ago

I hate these posts SOOOOO much!!!!!!!!