SQL is null and = null

Question

Possible duplicate:
what is "= null" and "IS NULL"
Is there a difference between IS NULL and = NULL

What's the difference between

where x is null 

and

 where x = null 

and why the latter do not work?

Answer 1

In SQL, comparing between a null value and any other value (including another null ) using a comparison operator (e.g. = != , < , Etc.) will result in null , which is considered false for the purpose of the where clause (strictly speaking, it is not true, not false, but the effect is the same).

The reason is that null means “unknown”, so the result of any comparison with null also “unknown”. This way, you won’t get hit on the lines if you where my_column = null .

SQL provides special syntax for checking if the column is null through is null and is not null , which is a special condition for checking for null (or not null ).

Here are some SQL showing the various conditions and their effects, as described above.

 create table t (x int, y int); insert into t values (null, null), (null, 1), (1, 1); select 'x = null' as test , x, y from t where x = null union all select 'x != null', x, y from t where x != null union all select 'not (x = null)', x, y from t where not (x = null) union all select 'x = y', x, y from t where x = y union all select 'not (x = y)', x, y from t where not (x = y); 

returns only 1 row (as expected):

 TEST XY x = y 1 1 

See how SQLFiddle works

Answer 2

It is important to note that NULL is not NULL .

NULL not a value and therefore cannot compare with another value.

where x is null checks if x is a null value.

where x = null checks if x is NULL, which will never be true

Answer 3

At first, the correct way is to check whether the value of the field is null , and later it will not work as you expect, because null is a special value that is not equal to anyone, so you cannot use equality comparison using = for it.

So when you need to check if the value of a field is null or not, use:

 where x is null 

instead:

 where x = null 

Answer 4

I think equality is something that can be absolutely defined. The problem with null is that it is essentially unknown. Null in combination with any other value is null - unknown. Query SQL "Is my value null ?" will be unknown every time, even if the input is null . I think the implementation of IS NULL clarifies the situation.