CODEWITHSUNDEEP
sqldatabasenull
Seattle Leonard
Seattle Leonard

Reputation: 6776

Why are the results for 1 = NULL and 1 != NULL the same?

The following

IF 1 = NULL 
    BEGIN
        SELECT  'A'
    END
ELSE 
    BEGIN
        SELECT  'B'
    END

Returns the result B as expected

Here's where things get really interesting

IF 1 != NULL 
    BEGIN
        SELECT  'A'
    END
ELSE 
    BEGIN
        SELECT  'B'
    END

Also returns B

Why is this the case?

Upvotes: 5

Views: 903

Answers (6)

Louie Bao
Louie Bao

Reputation: 1732

Agree with what everyone else has already said. Simply commenting from another angle, if you try setting ansi_nulls to off, you may get what you expected:

set ansi_nulls off

if 1 = null
    select 'a'
else
    select 'b' -- Returned


if 1 != null
    select 'a' -- Returned
else
    select 'b'

More info from Books Online:

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

That's ansi_nulls off explained. However, don't be tempted to simply switch it off because:

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Follow the below recommendation instead:

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

if 1 is null
    select 'a'
else
    select 'b' -- Returned


if 1 is not null
    select 'a' -- Returned
else
    select 'b'

Upvotes: 2

Matthew
Matthew

Reputation: 10444

It is neither equal to nor not-equal to NULL because NULL is not a thing but, in fact, the absence of a thing.

ANSI SQL 1999 (not MSSQL yet, though) includes a method called IS [NOT] DISTINCT FROM which can be used on NULL with the results you might expect.

How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?

Here is an excellent post on IS DISTINCT FROM behavior and the confusing nature of NULL in general by Itzik Ben-Gan

http://sqlmag.com/sql-server/not-distinct

The predicate can evaluate to TRUE, FALSE or UNKNOWN. The predicate evaluates to UNKNOWN whenever NULLs are involved.

Here is Erland Sommarskog wishing for it:

http://www.sommarskog.se/wishlist.html#isdistinctfrom

SQL:1999 defines the operators IS DISTINCT FROM and IS NOT DISTINCT FROM which are the same as <> and = respectively, except that they also applies to NULL values.

(From Erland) here is a link on Connect for MVP Steve Kass requesting the feature:

http://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate

This is a common requirement, but coding this for many columns is both tedious and error-prone (especially because of AND/OR precedence issues). Changing the setting of ANSI_NULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSI_NULLS to off is also non-standard and not granular enough to apply to specific comparisons in a single query.

Upvotes: 2

Zoran Horvat
Zoran Horvat

Reputation: 11301

It's simple. In ANSI terms, any expression (logical or arithmetical) involving NULL has unknown result:

(1 = NULL) IS UNKNOWN
(1 <> NULL) IS UNKNOWN
(1 + NULL) IS UNKNOWN
(1 * NULL) IS UNKNOWN

Therefore, in both cases you end up in the ELSE branch.

Try this link for more explanations.

Upvotes: 1

John
John

Reputation: 16007

Neither IF statement is true. NULL is neither equal to something nor not equal to something. Something either IS NULL or IS NOT NULL.

Upvotes: 6

Darren
Darren

Reputation: 70728

You can't compare NULL = NULL - it has no value.

SELECT 1 
WHERE NULL = NULL

Does not return anything

When comparing NULL values use IS not =

SELECT 1
WHERE NULL IS NULL

Reuturns 1

From MSDN:

To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.

http://msdn.microsoft.com/en-us/library/aa933227

Upvotes: 1

Fiisch
Fiisch

Reputation: 182

I guess NULL is specified that it is, in a way, uncomparable by <,>,=,!= operators. Such comparison probably returns NULL. Then the if(NULL) processing is skipped.

Upvotes: 0

Related Questions