SQL is null and = null [duplicate]

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 = nullunion allselect 'x != null', x, y from t where x != nullunion allselect 'not (x = null)', x, y from t where not (x = null)union allselect 'x = y', x, y from t where x = yunion allselect 'not (x = y)', x, y from t where not (x = y);

returns only 1 row (as expected):

TEST    X   Yx = y   1   1

See this running on SQLFiddle

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

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