Why does NULL = NULL evaluate to false in SQL server

**The confusion arises from the level of indirection (abstraction) that comes about from using NULL**.

Going back to the "what's under the Christmas tree" analogy, "Unknown" describes the state of knowledge about what is in Box A.

So if you don't know what's in Box A, you say it's "Unknown", but *that doesn't mean that "Unknown" is inside the box*. Something other than unknown is in the box, possibly some kind of object, or possibly nothing is in the box.

Similarly, if you don't know what's in Box B, you can label your state of knowledge about the contents as being "Unknown".

So here's the kicker: Your **state of knowledge about Box A is equal to your state of knowledge about Box B**. (Your state of knowledge in both cases is "Unknown" or "I don't know what's in the Box".) But the contents of the boxes may or may not be equal.

Going back to SQL, ideally you should only be able to compare values when you know what they are. **Unfortunately, the label that describes a lack of knowledge is stored in the cell itself**, so we're tempted to use it as a value. But we should not use that as a value, because it would lead to "the content of Box A equals the content of Box B when we don't know what's in Box A and/or we don't know what's in Box B.
(Logically, the implication "if I don't know what's in Box A and if I don't know what's in Box B, then what's in Box A = What's in Box B" is false.)

Yay, Dead Horse.

How old is Frank? I don't know (null).

How old is Shirley? I don't know (null).

Are Frank and Shirley the same age?

Correct answer should be "I don't know" (null), not "no", as Frank and Shirley *might* be the same age, we simply don't know.

Just an addition to other wonderful answers:

AND: The result of true and unknown is unknown, false and unknown is false,
while unknown and unknown is unknown.

OR: The result of true or unknown is true, false or unknown is unknown, while unknown or unknown is unknown.

NOT: The result of not unknown is unknown

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][1] per [Class][2] [Hierarchy][3] mapping, some values are just not mapped for certain classes.

- When mapping a [tree structure][4] 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.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

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.

Just because you don't know what two things are, does not mean they're equal. If when you think of `NULL` you think of “NULL” (string) then you probably want a different test of equality like Postgresql's `IS DISTINCT FROM` AND `IS NOT DISTINCT FROM`

From the [PostgreSQL docs on *"Comparison Functions and Operators"*](

[To see links please register here]

)

>expression `IS DISTINCT FROM` expression
>
>expression `IS NOT DISTINCT FROM` expression
>
>For non-null inputs, `IS DISTINCT FROM` is the same as the `<>` operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, `IS NOT DISTINCT FROM` is identical to `=` for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown".

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.

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)

MSDN has a nice descriptive <a href="http://msdn.microsoft.com/en-us/library/ms172138(VS.80).aspx">article</a> on nulls and the three state logic that they engender.

In short, the SQL92 spec defines NULL as unknown, and NULL used in the following operators causes unexpected results for the uninitiated:

= operator NULL true false
NULL NULL NULL NULL
true NULL true false
false NULL false true

and op NULL true false
NULL NULL NULL false
true NULL true false
false false false false

or op NULL true false
NULL NULL true NULL
true true true true
false NULL true false

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