SQL is null and null

Question

Possible Duplicate    what is  ldquo  null rdquo  and  ldquo  IS NULL rdquo    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

User · Answer

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

User · Answer

In SQL  a comparison between a null value and any other value  including another null  using a comparison operator  eg         lt   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

User · Answer

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

User · Answer

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

[sql] SQL is null and = null

Examples related to sql

Examples related to null