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?
Neither IF statement is true. NULL is neither equal to something nor not equal to something. Something either IS NULL or IS NOT NULL.
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:
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.
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'
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.
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.
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.
