CODEWITHSUNDEEP
sql-servert-sql
namko
namko

Reputation: 647

T SQL check null syntax confusion

I keep on writing the select statement where value is null in SQL server as 

SELECT * 
FROM TABLE_1 
WHERE VALUE = NULL

which is incorrect

Rather than

SELECT * 
FROM TABLE_1 
WHERE VALUE IS NULL

What is the reason why IS used to check the value when it is null but = is used when the value is an integer or string?

Upvotes: 1

Views: 106

Answers (3)

gotqn
gotqn

Reputation: 43636

In the context of SQL Server, NULL = NULL is true only in the following two cases:

  • when set operators are used (EXECEPT, INTERSECT, UNION, UNION ALL)
  • when values in unique constraints are checked

In all other cases, NULL = NULL is false.

That's the reason, it is safe to work with the SET operators when two result sets are manipulated and why we are allowed to have only one NULL value in an unique constraint.

Upvotes: 1

forpas
forpas

Reputation: 164089

Because VALUE=NULL gives a result of NULL, and if you use it in a WHERE statement it will be treated as false, just like if you used NOT VALUE=NULL which also evaluates to NULL.
Let me explain the above, just to clarify when I say treated as false.
If you use in your statement:

WHERE value = NULL

or 

WHERE NOT (value = NULL)

you will not get any rows.
If you use:

WHERE value = NULL AND id = 1

again you will not get any rows.
But if you use:

WHERE value = NULL OR id = 1

you will get 1 row, the one with id = 1.

NULL is an alias of missing or unknown and cannot be used in a place where a valid value like 1 or 'something' can be used.

Upvotes: 0

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

The reason is that NULL is not a value, but a lack of value. The ANSI standard states that the check NULL = NULL and NULL <> NULL both does not return true, but NULL. Think of NULL as "unknown value". If you compare 3=3, it will return true, but if you compare is some number, that I do not know, equal to some other number, which I do not know either, the the obvious result is I do not know are they equal.

You may want to take a look at ANSI_NULLS option too.

Upvotes: 9

Related Questions