Why does NULL = NULL evaluate to false in SQL server

The answers here all seem to come from a CS perspective so I want to add one from a developer perspective.

For a developer NULL is very useful. The answers here say NULL means unknown, and maybe in CS theory that's true, don't remember, it's been a while. In actual development though, at least in my experience, that happens about 1% of the time. The other 99% it is used for cases where the value is not UNKNOWN but it is KNOWN TO BE ABSENT.

For example:

• Client.LastPurchase, for a new client. It is not unknown, it is known that he hasn't made a purchase yet.

• When using an ORM with a Table per Class Hierarchy mapping, some values are just not mapped for certain classes.

• When mapping a tree structure a root will usually have Parent = NULL

• And many more...

I'm sure most developers at some point wrote WHERE value = NULL, didn't get any results, and that's how they learned about IS NULL syntax. Just look how many votes this question and the linked ones have.

SQL Databases are a tool, and they should be designed the way which is easiest for their users to understand.

There are two sensible ways to handle NULL = NULL comparisons in a WHERE clause, and they boil down to "What do you mean by NULL?" One way assumes NULL means "unknown," and the other assumes NULL means "data does not exist." SQL has chosen a third way which is wrong all around.

The "NULL means unknown" solution: Throw an error.

Unknown = unknown should evaluate to 3VL null. But the output of a WHERE clause is 2VL: You either return the row or you don't. It's like being asked to divide by zero and return a number: There is no correct response. So you throw an error instead, and force the programmer to explicitly handle this situation.

The "NULL means no data" solution: Return the row.

No data = no data should evaluate to true. If I'm comparing two people, and they have the same first name, and the same last name, and neither has a middle name, then it is correct to say "These people have the same name."

The SQL solution: Don't return the row.

This is always wrong. If NULL means "unknown," then you don't know if the row should be returned or not, and you should not try to guess. If NULL means "no data," then you should return the row. Either way, silently removing the row is incorrect and will cause problems. It's the worst of both worlds.

Setting aside theory and speaking in practical terms, I'm with AlexDev: I have almost never encountered a case where "return the row" was not the desired result. However, "almost never" is not "never," and SQL databases often serve as the backbones of big important systems, so I can see a fair case for being rigorous and throwing an error.

What I cannot see is a case for silently coercing 3VL null into 2VL false. Like most silent type coercions, it's a rabid weasel waiting to be set loose in your system, and when the weasel finally jumps out and bites someone, you'll have the merry devil of a time tracking it back to its nest.

You work for the government registering information about citizens. This includes the national ID for every person in the country. A child was left at the door of a church some 40 years ago, nobody knows who their parents are. This person's father ID is NULL. Two such people exist. Count people who share the same father ID with at least one other person (people who are siblings). Do you count those two too?

The answer is no, you don’t, because we don’t know if they are siblings or not.

Suppose you don’t have a NULL option, and instead use some pre-determined value to represent “the unknown”, perhaps an empty string or the number 0 or a * character, etc. Then you would have in your queries that * = *, 0 = 0, and “” = “”, etc. This is not what you want (as per the example above), and as you might often forget about these cases (the example above is a clear fringe case outside ordinary everyday thinking), then you need the language to remember for you that NULL = NULL is not true.

Necessity is the mother of invention.

If you are looking for an expression returning true for two NULLs you can use:

SELECT 1 
WHERE EXISTS (
    SELECT NULL
    INTERSECT
    SELECT NULL
)

It is helpful if you want to replicate data from one table to another.

The equality test, for example, in a case statement when clause, can be changed from

XYZ = NULL 

to

XYZ IS NULL

If I want to treat blanks and empty string as equal to NULL I often also use an equality test like:

(NULLIF(ltrim( XYZ ),'') IS NULL)

To quote the Christmas analogy again:

In SQL, NULL basically means "closed box" (unknown). So, the result of comparing two closed boxes will also be unknown (null).

I understand, for a developer, this is counter-intuitive, because in programming languages, often NULL rather means "empty box" (known). And comparing two empty boxes will naturally yield true / equal.

This is why JavaScript for example distinguishes between null and undefined.

Null isn't equal to anything including itself Best way to test if an object is null is to check whether the object equals itself since null is the only object not equal to itself

const obj = null

console.log(obj==obj) //false, then it's null

Check this article