SQL Is Null and = Null

SQL is null and = null

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

Why does NULL = NULL evaluate to false in SQL server

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

EDIT:
This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'


set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

Difference between is not null and Null in SQL Server?

1) First question about difference IS NULL vs = NULL:

Comparison operators like (=, <>, <, >, ...) with NULL always produce NULL.
Use IS NULL/IS NOT NULL instead.

2) Second question "why (null = null) is false":

From SQL and the Snare of Three-Valued Logic:

One kind of NULL marks values which are:

missing because the value is
unknown

and the other kind marks values that are missing because the
attribute is missing.

When you try to compare NULL you actualy do something like

UNKNOWN = UNKNOWN

This is of course unknown.

Oracle: What's the difference between NULL and null?

<null> is not part of oracle syntax, it is probably used by your IDE or your console for printing out null values in query results.
The only valid representation of a null value actually is NULL. Try it out:

-- OK:
select NULL from dual   

-- Syntax error:
select <null> from dual

-- Syntax OK, but not a null value:
select '<null>' from dual

Why in SQL NULL can't match with NULL?

Rule : Not even a NULL can be equal to NULL.

A Non-Technical aspect

If you ask two girls, how old they are? may be you would hear them to refuse to answer your question,
Both girls are giving you NULL as age and this doesn't mean both have similar age.
So there is nothing can be equal to null.

Difference between x = null vs. x IS NULL

This particular case is well-described in Snowflake's documentation:

EQUAL_NULL

IS [ NOT ] DISTINCT FROM

Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (=), which treats NULLs as unknown values.

+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+
| X1_I | X2_I | X1.I IS NOT DISTINCT FROM X2.I | SELECT IF X1.I IS NOT DISTINCT FROM X2.I | X1.I IS DISTINCT FROM X2.I | SELECT IF X1.I IS DISTINCT FROM X2.I |
|------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------|
|    1 |    1 | True                           | Selected                                 | False                      | Not                                  |
|    1 |    2 | False                          | Not                                      | True                       | Selected                             |
|    1 | NULL | False                          | Not                                      | True                       | Selected                             |
|    2 |    1 | False                          | Not                                      | True                       | Selected                             |
|    2 |    2 | True                           | Selected                                 | False                      | Not                                  |
|    2 | NULL | False                          | Not                                      | True                       | Selected                             |
| NULL |    1 | False                          | Not                                      | True                       | Selected                             |
| NULL |    2 | False                          | Not                                      | True                       | Selected                             |
| NULL | NULL | True                           | Selected                                 | False                      | Not                                  |
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

---

Related Topics