Why using predicates when doing a left join query is not the same as using conditions

by John McGeechan

Mysql

 

 

 

 



The title of this post is probably self evident for most SQL users, but was a timely reminder for me that something that looks correct code-wise can actually can be very wrong.

OK this is a fairly simple gotcha (OK more a case of me rushing things and not looking at what I was doing, but still maybe worth a quick post) > let's take two tables...

table1

id animal
1 dog
2 cat
3 cow

table2

animal_id likes value
1 food bones
2 food fish
3 food grass
1 to chase postmen
2 to chase mice

Ok so I want to bring back all the animals with anything they like to chase. If an animal does not like to chase, still return it. So let's do a left join, this should work, right ?

select a.animal,b.value
from
table1 a
left join table2 b on a.id =  b.animal_id 
where b.likes = 'to chase'

Er...well no. As I am sure many have spotted, here's the result....

animal value
dog postmen
cat mice

Oops, nice try, no cow. The use of the predicate here is not the same as using it on the join itself. Now try this....

select a.animal,b.value
from
table1 a
left join table2 b on a.id =  b.animal_id and b.likes = 'to chase'  

Gives the desired result....

animal value
dog postmen
cat mice
cow null

Post new comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.