SQL is null and = null
null, sql
Solution
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
Problem
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?