Why Does Null = Null Evaluate to False in SQL Server

Why does NULL = NULL evaluate to false in SQL server

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'

Why SQL does finally treat a NULL value as FALSE?

Within the context of a SELECT statement (so, in ON clauses, the WHERE clause, and within CASE expressions), predicates must be TRUE (not FALSE or UNKNOWN¹) for the predicate to be satisfied.

However, within CHECK constraints, predicates must not be FALSE in order to be satisfied.

I.e. the following script will work:

CREATE TABLE T (
  ID int not null,
  Val varchar(10) null,
  constraint CK_Vals CHECK (Val in ('abc','def'))
);
INSERT INTO T(ID,Val) VALUES (10,NULL);

So we can see that it is not universally true in SQL that UNKNOWN results are treated as FALSE. It's also trivially demonstrated by the fact that wrapping a predicate that produces UNKNOWN with NOT (<existing predicate>) does not produce TRUE.

The wikipedia page on Three-Valued logic covers a lot of details.

---

¹I'm assuming your question is about UNKNOWN rather than NULL, since you've tagged sql and relational-algebra. In standard SQL, UNKNOWN and NULL are two distinctly different concepts. Only (so far as I'm aware) mysql conflates the two.

Why is `NOT(NULL=NULL)` false?

The three-valued logic (3VL) defines the logical operators as:

+---------+---------+---------+---------+---------+
| p       | q       | p OR q  | p AND q | p = q   |
+---------+---------+---------+---------+---------+
| True    | Unknown | True    | Unknown | Unknown |
| False   | Unknown | Unknown | False   | Unknown |
| Unknown | True    | True    | Unknown | Unknown |
| Unknown | False   | Unknown | False   | Unknown |
| Unknown | Unknown | Unknown | Unknown | Unknown |
+---------+---------+---------+---------+---------+

The NOT behavior has the following truth table:

+---------+---------+
| p       | NOT p   |
+---------+---------+
| True    | False   |
| False   | True    |
| Unknown | Unknown |
+---------+---------+

So, in the expression NOT(NULL = NULL), you get:

NULL = NULL -> Unknown
NOT(Unknown) -> Unknown

Your case condition always acts like not fulfilled because your expression evaluates to Unknown, i.e. neither true nor false.

For more information on the way SQL Server works regarding nulls, have a look at Why does NULL = NULL evaluate to false in SQL server

Why NULL is not equal to anything is a false statement?

Almost any comparison to NULL returns NULL. This is because NULL has the semantics of "unknown value" rather than "missing".

When you have the comparison 9 <> NULL, then the returned value is NULL (for the comparison).

WHERE clauses treat NULL values as "false" so rows get filtered out. Similarly, CASE expressions treat NULL values as "false".

This is not always the case. CHECK constraints treat NULL values as "true", so the check constraint passes even when the values are NULL.

SQL Server: Why does comparison null=value return true for NOT IN?

Common problem, canned answer:

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query:

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns nothing. This may look counterintuitive to a beginner database programmer, but it actually makes perfect sense. The explanation consist of several simple steps. First of all, consider the following two queries, which are clearly equivalent:

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName IN('Hedlund', 'Holloway', NULL)



SELECT LastName, FirstName FROM Person.Contact

WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL

Note that both queries return expected results. Now, let us recall DeMorgan's theorem, which states that:

not (P and Q) = (not P) or (not Q)

not (P or Q) = (not P) and (not Q)

I am cutting and pasting from Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). Applying DeMorgan's theorem to this queries, it follows that these two queries are also equivalent:

SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)



SELECT LastName, FirstName FROM Person.Contact

WHERE LastName<>'Hedlund' AND LastName<>'Holloway' AND LastName<>NULL

This last LastName<>NULL can never be true

why is null not equal to null false

relational expressions involving NULL actually yield NULL again

edit

here, <> stands for arbitrary binary operator, NULL is the SQL placeholder, and value is any value (NULL is not a value):

• NULL <> value -> NULL
• NULL <> NULL -> NULL

the logic is: NULL means "no value" or "unknown value", and thus any comparison with any actual value makes no sense.

is X = 42 true, false, or unknown, given that you don't know what value (if any) X holds? SQL says it's unknown. is X = Y true, false, or unknown, given that both are unknown? SQL says the result is unknown. and it says so for any binary relational operation, which is only logical (even if having NULLs in the model is not in the first place).

SQL also provides two unary postfix operators, IS NULL and IS NOT NULL, these return TRUE or FALSE according to their operand.

• NULL IS NULL -> TRUE
• NULL IS NOT NULL -> FALSE

Is there a setting in SQL Server to have null = null evaluate to true?

I will avoid the ansi_nulls setting and use isnull function although it could complicate certain queries.

What is 'where 1=1' and 'where null=null' in sql server

To the questions part about "why" using this:

Since 1=1 always evaluates to true and true is the neutral element for logical AND operation, it is often used for dynamically built queries.

The problem with dynamically built queries that are using multiple AND operations added or not to the query on some conditions ist that you've to keep track about it is the first condition added or not. (i.e. SELECT * FROM mytable WHERE AND bla = 'blub' is not valid; we must suppress the AND with the first condition added to the query)

All this can be avoided when we change the queries base to SELECT * from mytable WHERE 1=1. Now we can add our AND conditions dynamically without considering if it is the first condition added or not. (i.e. SELECT * FROM mytable WHERE 1=1 AND bla = 'blub' is valid)

why does 10/NULL evaluate to null?

Shouldn't 10/NULL also be considered as FALSE?

No, because:

Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

Emphasis mine, taken from the Oracle manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm#i59110

And this is required by the SQL standard.

Edit, as the question was for RDBMS in general:

SQL Server

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN

Link to the the manual:

MySQL

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for a particular function or operator

Link to the manual

DB2

if either operand can be null, the result can be null, and if either is null, the result is the null value

Link to the manual:

PostgreSQL

Unfortunately I could not find such an explicit statement in the PostgreSQL manual, although I sure it behaves the same.

---

Warning: The "(except concatenation)" is an Oracle only and non-standard exception. (The empty string and NULL are almost identical in Oracle). Concatenating nulls gives null in all other DBMS.

---

Related Topics