Why do we have to use IS NULL instead of = NULL?

database-agnosticnull

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

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 = NULL?

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 IS into =? 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.

Best Answer

The question is when is a comparison NULL safe

It depends on

ANSI convention that NULL is an unknown value and cannot be compared with any other value, including other NULLs.

See for mysql https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

And here for sql server https://docs.microsoft.com/en-us/sql/t-sql/language-elements/equals-transact-sql?view=sql-server-ver15

IS NULL is NULL safe and will always get the correct answer on most Databases, independent of sql modes