I don't know if this is the case for all database implementations but it is for SQL Server and MySQL which are the two I've worked with.
I've read articles about the topic and the manuals of SQL Server and MySQL and the reasoning always boils down to semantics around the meaning of the word
NULL and how it represents an unknown and therefore nothing can be equal to
What I'd like to do here though is set aside semantics, legacy code considerations and existing developer expectations and see if there are any practical arguments against allowing
= NULL. So what I mean is, if we were starting with a clean slate, had no legacy considerations, why would we not want to allow
In my 20 years of dev experience I've never encountered a situation in which
IS NULL has been anything but an annoyance, an extra condition that I have to handle separately from everything else.
So can someone give me an explanation of why the following examples shouldn't work as a naive, new dev might expect them to?
SELECT * FROM MyTable WHERE myField = NULL
SELECT * FROM MyTable WHERE myField <> NULL
SELECT * FROM MyTable WHERE myField IN (1, 2, 3, NULL)
What is the danger/problem here with collapsing
=? If these admittedly trivial examples would be fine but there are other examples where it would be a problem, feel free to bring those up.
I've never gotten a satisfactory answer to this and every time I'm writing a query and this comes up it gnaws at me.
The question is meant to be general and not tied to a specific implementation.