Yesterday afternoon’s little bit of head scratching and debugging confusion comes to you courtesy of the fact that

SELECT * FROM my_table
WHERE my_id = iObject.my_id

does not do the expected thing when both my_id and iObject.my_id are null.
Because apparently null = null and null <> null both evaluate to false, because any comparison involving null evaluates to false.
(Edit: As pointed out to me by many people, it should actually evaluate to null, but regardless, the effect is the same… the select statement does not get the rows you expect.)

I guess it makes sense when you think about it (a lack of a value is not equal to the lack of a value, because there is no value, as opposed to C where null is just a pointer to 0x0 so a value comparison makes sense), but it’s still a surprising issue when you first come across it. Working around it involves the non-intuitive

SELECT * FROM my_table
WHERE (my_id IS NULL AND iObject.my_id IS NULL) OR my_id = iObject.my_id

It looks like MySql also provides the null-comparing operator <=> which will return equality even if both items are null. But it doesn’t look like Oracle does.


On an unrelated note, I’ve redone the design of photos. Check it out if you haven’t yet, and let me know if things are broken or don’t work for you. :)

2 thoughts on “null

  1. if you like having a ton of extra operators with very specific meaning (like the ), you will love haskell

Comments are closed.