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 |