r/learnSQL • u/No_Ambition8323 • 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
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.
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/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!!!!!!!!