SQL is null and = null [duplicate]

Question

Possible Duplicate:
what is “=null” and “ IS NULL”
Is there any difference between IS NULL and =NULL

What is the difference between

where x is null

and

where x = null

and why does the latter not work?

Answer 1

It's important to note, that NULL doesn't equal NULL.

NULL is not a value, and therefore cannot be compared to another value.

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

where x = null is checking whether x equals NULL, which will never be true

Answer 2

First is correct way of checking whether a field value is null while later won't work the way you expect it to because null is special value which does not equal anything, so you can't use equality comparison using = for it.

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

where x is null

instead of:

where x = null

Answer 3

i think that equality is something that can be absolutely determined. The trouble with null is that it's inherently unknown. null combined with any other value is null - unknown. Asking SQL "Is my value equal to null?" would be unknown every single time, even if the input is null. I think the implementation of IS NULL makes it clear.

Answer 4

In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).

The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).

Here's some SQL showing a variety of conditions and and their effect as per 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    X   Y
x = y   1   1

See this running on SQLFiddle