Why does NULL = NULL evaluate to false in SQL server

Question

In SQL server if you have nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywords are the correct way to do it. But why does SQL server behave this way?

Answer 1

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

http://www.postgresql.org/docs/8.4/static/functions-comparison.html

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

Answer 2

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

EDIT: This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
 print 'true'
else
 print 'false'


set ansi_nulls ON

if null = null
 print 'true'
else
 print 'false'

Answer 3

Maybe it depends, but I thought NULL=NULL evaluates to NULL like most operations with NULL as an operand.

Answer 4

Does it actually evaluate to false? That would be really counterintuitive... It should evaluate to NULL.

Answer 5

NULL isn't equal to anything, not even itself. My personal solution to understanding the behavior of NULL is to avoid using it as much as possible :).

Answer 6

null is unknown in sql so we cant expect two unknowns to be same.

However you can get that behavior by setting ANSI_NULLS to Off(its On by Default) You will be able to use = operator for nulls

SET ANSI_NULLS off
if null=null
print 1
else 
print 2
set ansi_nulls on
if null=null
print 1
else 
print 2

Answer 7

MSDN has a nice descriptive article on nulls and the three state logic that they engender.

In short, the SQL92 spec defines NULL as unknown, and NUL 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

Answer 8

The question:
Does one unknown equal another unknown?
(NULL = NULL)
That question is something no one can answer so it defaults to true or false depending on your ansi_nulls setting.

However the question:
Is this unknown variable unknown?
This question is quite different and can be answered with true.

nullVariable = null is comparing the values
nullVariable is null is comparing the state of the variable

Answer 9

Here I will hopefully clarify my position.

That NULL = NULL evaluate to FALSE is wrong. Hacker and Mister correctly answered NULL. Here is why. Dewayne Christensen wrote to me, in a comment to Scott Ivey:

Since it's December, let's use a seasonal example. I have two presents under the tree. Now, you tell me if I got two of the same thing or not.

They can be different or they can be equal, you don't know until one open both presents. Who knows? You invited two people that don't know each other and both have done to you the same gift - rare, but not impossible §.

So the question: are these two UNKNOWN presents the same (equal, =)? The correct answer is: UNKNOWN (i.e. NULL).

This example was intended to demonstrate that "..(false or null, depending on your system).." is a correct answer - it is not, only NULL is correct in 3VL (or is ok for you to accept a system which gives wrong answers?)

A correct answer to this question must emphasize this two points:

• three-valued logic (3VL) is counterintuitive (see countless other questions on this subject on Stackoverflow and in other forum to make sure);
• SQL-based DBMSes often do not respect even 3VL, they give wrong answers sometimes (as, the original poster assert, SQL Server do in this case).

So I reiterate: SQL does not any good forcing one to interpret the reflexive property of equality, which state that:

for any x, x = x §§ (in plain English: whatever the universe of discourse, a "thing" is always equal to itself).

.. in a 3VL (TRUE, FALSE, NULL). The expectation of people would conform to 2VL (TRUE, FALSE, which even in SQL is valid for all other values), i.e. x = x always evaluate to TRUE, for any possible value of x - no exceptions.

Note also that NULLs are valid " non-values " (as their apologists pretend them to be) which one can assign as attribute values(??) as part of relation variables. So they are acceptable values of every type (domain), not only of the type of logical expressions.

And this was my point: NULL, as value, is a "strange beast". Without euphemism, I prefer to say: nonsense.

I think that this formulation is much more clear and less debatable - sorry for my poor English proficiency.

This is only one of the problems of NULLs. Better to avoid them entirely, when possible.

§ we are concerned about values here, so the fact that the two presents are always two different physical objects are not a valid objection; if you are not convinced I'm sorry, it is not this the place to explain the difference between value and "object" semantics (Relational Algebra has value semantics from the start - see Codd's information principle; I think that some SQL DBMS implementors don't even care about a common semantics).

§§ to my knowledge, this is an axiom accepted (in a form or another, but always interpreted in a 2VL) since antiquity and that exactly because is so intuitive. 3VLs (is a family of logics in reality) is a much more recent development (but I'm not sure when was first developed).

Side note: if someone will introduce Bottom, Unit and Option Types as attempts to justify SQL NULLs, I will be convinced only after a quite detailed examination that will shows of how SQL implementations with NULLs have a sound type system and will clarify, finally, what NULLs (these "values-not-quite-values") really are.

---

In what follow I will quote some authors. Any error or omission is probably mine and not of the original authors.

Joe Celko on SQL NULLs

I see Joe Celko often cited on this forum. Apparently he is a much respected author here. So, I said to myself: "what does he wrote about SQL NULLs? How does he explain NULLs numerous problems?". One of my friend has an ebook version of Joe Celko's SQL for smarties: advanced SQL programming, 3rd edition. Let's see.

First, the table of contents. The thing that strikes me most is the number of times that NULL is mentioned and in the most varied contexts:

3.4 Arithmetic and NULLs 109
3.5 Converting Values to and from NULL 110
3.5.1 NULLIF() Function 110
6 NULLs: Missing Data in SQL 185
6.4 Comparing NULLs 190
6.5 NULLs and Logic 190
6.5.1 NULLS in Subquery Predicates 191
6.5.2 Standard SQL Solutions 193
6.6 Math and NULLs 193
6.7 Functions and NULLs 193
6.8 NULLs and Host Languages 194
6.9 Design Advice for NULLs 195
6.9.1 Avoiding NULLs from the Host Programs 197
6.10 A Note on Multiple NULL Values 198
10.1 IS NULL Predicate 241
10.1.1 Sources of NULLs 242
...

and so on. It rings "nasty special case" to me.

I will go into some of these cases with excerpts from this book, trying to limit myself to the essential, for copyright reasons. I think these quotes fall within "fair use" doctrine and they can even stimulate to buy the book - so I hope that no one will complain (otherwise I will need to delete most of it, if not all). Furthermore, I shall refrain from reporting code snippets for the same reason. Sorry about that. Buy the book to read about datailed reasoning.

Page numbers between parenthesis in what follow.

NOT NULL Constraint (11)

The most important column constraint is the NOT NULL, which forbids the use of NULLs in a column. Use this constraint routinely, and remove it only when you have good reason. It will help you avoid the complications of NULL values when you make queries against the data.

It is not a value; it is a marker that holds a place where a value might go.

Again this "value but not quite a value" nonsense. The rest seems quite sensible to me.

(12)

In short, NULLs cause a lot of irregular features in SQL, which we will discuss later. Your best bet is just to memorize the situations and the rules for NULLs when you cannot avoid them.

Apropos of SQL, NULLs and infinite:

(104) CHAPTER 3: NUMERIC DATA IN SQL

SQL has not accepted the IEEE model for mathematics for several reasons.

...

If the IEEE rules for math were allowed in SQL, then we would need type conversion rules for infinite and a way to represent an infinite exact numeric value after the conversion. People have enough trouble with NULLs, so let’s not go there.

SQL implementations undecided on what NULL really means in particular contexts:

3.6.2 Exponential Functions (116)

The problem is that logarithms are undefined when (x <= 0). Some SQL implementations return an error message, some return a NULL and DB2/ 400; version 3 release 1 returned *NEGINF (short for “negative infinity”) as its result.

Joe Celko quoting David McGoveran and C. J. Date:

6 NULLs: Missing Data in SQL (185)

In their book A Guide to Sybase and SQL Server, David McGoveran and C. J. Date said: “It is this writer’s opinion than NULLs, at least as currently defined and implemented in SQL, are far more trouble than they are worth and should be avoided; they display very strange and inconsistent behavior and can be a rich source of error and confusion. (Please note that these comments and criticisms apply to any system that supports SQL-style NULLs, not just to SQL Server specifically.)”

NULLs as a drug addiction:

(186/187)

In the rest of this book, I will be urging you not to use them, which may seem contradictory, but it is not. Think of a NULL as a drug; use it properly and it works for you, but abuse it and it can ruin everything. Your best policy is to avoid NULLs when you can and use them properly when you have to.

My unique objection here is to "use them properly", which interacts badly with specific implementation behaviors.

6.5.1 NULLS in Subquery Predicates (191/192)

People forget that a subquery often hides a comparison with a NULL. Consider these two tables:

...

The result will be empty. This is counterintuitive, but correct.

(separator)

6.5.2 Standard SQL Solutions (193)

SQL-92 solved some of the 3VL (three-valued logic) problems by adding a new predicate of the form:

<search condition> IS [NOT] TRUE | FALSE | UNKNOWN

But UNKNOWN is a source of problems in itself, so that C. J. Date, in his book cited below, reccomends in chapter 4.5. Avoiding Nulls in SQL:

• Don't use the keyword UNKNOWN in any context whatsoever.

Read "ASIDE" on UNKNOWN, also linked below.

6.8 NULLs and Host Languages (194)

However, you should know how NULLs are handled when they have to be passed to a host program. No standard host language for which an embedding is defined supports NULLs, which is another good reason to avoid using them in your database schema.

(separator)

6.9 Design Advice for NULLs (195)

It is a good idea to declare all your base tables with NOT NULL constraints on all columns whenever possible. NULLs confuse people who do not know SQL, and NULLs are expensive.

Objection: NULLs confuses even people that know SQL well, see below.

(195)

NULLs should be avoided in FOREIGN KEYs. SQL allows this “benefit of the doubt” relationship, but it can cause a loss of information in queries that involve joins. For example, given a part number code in Inventory that is referenced as a FOREIGN KEY by an Orders table, you will have problems getting a listing of the parts that have a NULL. This is a mandatory relationship; you cannot order a part that does not exist.

(separator)

6.9.1 Avoiding NULLs from the Host Programs (197)

You can avoid putting NULLs into the database from the Host Programs with some programming discipline.

...

1. Determine impact of missing data on programming and reporting: Numeric columns with NULLs are a problem, because queries using aggregate functions can provide misleading results.

(separator)

(227)

The SUM() of an empty set is always NULL. One of the most common programming errors made when using this trick is to write a query that could return more than one row. If you did not think about it, you might have written the last example as: ...

(separator)

10.1.1 Sources of NULLs (242)

It is important to remember where NULLs can occur. They are more than just a possible value in a column. Aggregate functions on empty sets, OUTER JOINs, arithmetic expressions with NULLs, and OLAP operators all return NULLs. These constructs often show up as columns in VIEWs.

(separator)

(301)

Another problem with NULLs is found when you attempt to convert IN predicates to EXISTS predicates.

(separator)

16.3 The ALL Predicate and Extrema Functions (313)

It is counterintuitive at first that these two predicates are not the same in SQL:

...

But you have to remember the rules for the extrema functions—they drop out all the NULLs before returning the greater or least values. The ALL predicate does not drop NULLs, so you can get them in the results.

(separator)

(315)

However, the definition in the standard is worded in the negative, so that NULLs get the benefit of the doubt. ...

As you can see, it is a good idea to avoid NULLs in UNIQUE constraints.

Discussing GROUP BY:

NULLs are treated as if they were all equal to each other, and form their own group. Each group is then reduced to a single row in a new result table that replaces the old one.

This means that for GROUP BY clause NULL = NULL does not evaluate to NULL, as in 3VL, but it evaluate to TRUE.

SQL standard is confusing:

The ORDER BY and NULLs (329)

Whether a sort key value that is NULL is considered greater or less than a non-NULL value is implementation-defined, but...

... There are SQL products that do it either way.

In March 1999, Chris Farrar brought up a question from one of his developers that caused him to examine a part of the SQL Standard that I thought I understood. Chris found some differences between the general understanding and the actual wording of the specification.

And so on. I think is enough by Celko.

C. J. Date on SQL NULLs

C. J. Date is more radical about NULLs: avoid NULLs in SQL, period. In fact, chapter 4 of his SQL and Relational Theory: How to Write Accurate SQL Code is titled "NO DUPLICATES, NO NULLS", with subchapters "4.4 What's Wrong with Nulls?" and "4.5 Avoiding Nulls in SQL" (follow the link: thanks to Google Books, you can read some pages on-line).

Fabian Pascal on SQL NULLs

From its Practical Issues in Database Management - A Reference for the Thinking Practitioner (no excerpts on-line, sorry):

10.3 Pratical Implications

10.3.1 SQL NULLs

... SQL suffers from the problems inherent in 3VL as well as from many quirks, complications, counterintuitiveness, and outright errors [10, 11]; among them are the following:

• Aggregate functions (e.g., SUM(), AVG()) ignore NULLs (except for COUNT()).
• A scalar expression on a table without rows evaluates incorrectly to NULL, instead of 0.
• The expression "NULL = NULL" evaluates to NULL, but is actually invalid in SQL; yet ORDER BY treats NULLs as equal (whatever they precede or follow "regular" values is left to DBMS vendor).
• The expression "x IS NOT NULL" is not equal to "NOT(x IS NULL)", as is the case in 2VL.

...

All commercially implemented SQL dialects follow this 3VL approach, and, thus, not only do they exibits these problems, but they also have spefic implementation problems, which vary across products.

Answer 10

The concept of NULL is questionable, to say the least. Codd introduced the relational model and the concept of NULL in context (and went on to propose more than one kind of NULL!) However, relational theory has evolved since Codd's original writings: some of his proposals have since been dropped (e.g. primary key) and others never caught on (e.g. theta operators). In modern relational theory (truly relational theory, I should stress) NULL simply does not exist. See The Third Manifesto. http://www.thethirdmanifesto.com/

The SQL language suffers the problem of backwards compatibility. NULL found its way into SQL and we are stuck with it. Arguably, the implementation of NULL in SQL is flawed (SQL Server's implementation makes things even more complicated due to its ANSI_NULLS option).

I recommend avoiding the use of NULLable columns in base tables.

---

Although perhaps I shouldn't be tempted, I just wanted to assert a corrections of my own about how NULL works in SQL:

NULL = NULL evaluates to UNKNOWN.

UNKNOWN is a logical value.

NULL is a data value.

This is easy to prove e.g.

SELECT NULL = NULL

correctly generates an error in SQL Server. If the result was a data value then we would expect to see NULL, as some answers here (wrongly) suggest we would.

The logical value UNKNOWN is treated differently in SQL DML and SQL DDL respectively.

In SQL DML, UNKNOWN causes rows to be removed from the resultset.

For example:

CREATE TABLE MyTable
(
 key_col INTEGER NOT NULL UNIQUE, 
 data_col INTEGER
 CHECK (data_col = 55)
);

INSERT INTO MyTable (key_col, data_col)
   VALUES (1, NULL);

The INSERT succeeds for this row, even though the CHECK condition resolves to NULL = NULL. This is due defined in the SQL-92 ("ANSI") Standard:

11.6 table constraint definition

3)

If the table constraint is a check constraint definition, then let SC be the search condition immediately contained in the check constraint definition and let T be the table name included in the corresponding table constraint descriptor; the table constraint is not satisfied if and only if

EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )

is true.

Read that again carefully, following the logic.

In plain English, our new row above is given the 'benefit of the doubt' about being UNKNOWN and allowed to pass.

In SQL DML, the rule for the WHERE clause is much easier to follow:

The search condition is applied to each row of T. The result of the where clause is a table of those rows of T for which the result of the search condition is true.

In plain English, rows that evaluate to UNKNOWN are removed from the resultset.