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...
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....
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....