by John McGeechan

 

 

 

 

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