Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL?

SELECT开发者_C百科 * FROM MyTable WHERE MyColumn != NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

<> 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.

NULL has no value, and so cannot be compared using the scalar value operators.

In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.

Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.

Note that this behavior is the default (ANSI) behavior.

If you:

 SET ANSI_NULLS OFF

http://msdn.microsoft.com/en-us/library/ms188048.aspx

You'll get different results.

SET ANSI_NULLS OFF will apparently be going away in the future...

We use

SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';

to return all rows where MyColumn is NULL or all rows where MyColumn is an empty string. To many an "end user", the NULL vs. empty string issue is a distinction without a need and point of confusion.

In SQL, anything you evaluate / compute with NULL results into UNKNOWN

This is why SELECT * FROM MyTable WHERE MyColumn != NULL or SELECT * FROM MyTable WHERE MyColumn <> NULL gives you 0 results.

To provide a check for NULL values, isNull function is provided.

Moreover, you can use the IS operator as you used in the third query.

The only test for NULL is IS NULL or IS NOT NULL. Testing for equality is nonsensical because by definition one doesn't know what the value is.

Here is a wikipedia article to read:

https://en.wikipedia.org/wiki/Null_(SQL)

NULL Cannot be compared to any value using the comparison operators. NULL = NULL is false. Null is not a value. The IS operator is specially designed to handle NULL comparisons.

null represents no value or an unknown value. It doesn’t specify why there is no value, which can lead to some ambiguity.

Suppose you run a query like this:

SELECT *
FROM orders
WHERE delivered=ordered;

that is, you are looking for rows where the ordered and delivered dates are the same.

What is to be expected when one or both columns are null?

Because at least one of the dates is unknown, you cannot expect to say that the 2 dates are the same. This is also the case when both dates are unknown: how can they be the same if we don’t even know what they are?

For this reason, any expression treating null as a value must fail. In this case, it will not match. This is also the case if you try the following:

SELECT *
FROM orders
WHERE delivered<>ordered;

Again, how can we say that two values are not the same if we don’t know what they are.

SQL has a specific test for missing values:

IS NULL

Specifically it is not comparing values, but rather it seeks out missing values.

Finally, as regards the != operator, as far as I am aware, it is not actually in any of the standards, but it is very widely supported. It was added to make programmers from some languages feel more at home. Frankly, if a programmer has difficulty remembering what language they’re using, they’re off to a bad start.

I would like to suggest this code I made to find if there is a change in a value, i being the new value and d being the old (although the order does not matter). For that matter, a change from value to null or vice versa is a change but from null to null is not (of course, from value to another value is a change but from value to the same it is not).

CREATE FUNCTION [dbo].[ufn_equal_with_nulls]
(
    @i sql_variant,
    @d sql_variant
)
RETURNS bit
AS
BEGIN
    DECLARE @in bit = 0, @dn bit = 0
    if @i is null set @in = 1
    if @d is null set @dn = 1

    if @in <> @dn
        return 0

    if @in = 1 and @dn = 1
        return 1

    if @in = 0 and @dn = 0 and @i = @d
        return 1

    return 0

END

To use this function, you can

declare @tmp table (a int, b int)
insert into @tmp values
(1,1),
(1,2),
(1,null),
(null,1),
(null,null)

---- in select ----
select *, [dbo].[ufn_equal_with_nulls](a,b) as [=] from @tmp

---- where equal ----
select *,'equal' as [Predicate] from @tmp where  [dbo].[ufn_equal_with_nulls](a,b) = 1

---- where not equal ----
select *,'not equal' as [Predicate] from @tmp where  [dbo].[ufn_equal_with_nulls](a,b) = 0

The results are:

---- in select ----
a   b   =
1   1   1
1   2   0
1   NULL    0
NULL    1   0
NULL    NULL    1

---- where equal ----
1   1   equal
NULL    NULL    equal

---- where not equal ----
1   2   not equal
1   NULL    not equal
NULL    1   not equal

The usage of sql_variant makes it compatible for variety of types

NULL is not anything...it is unknown. NULL does not equal anything. That is why you have to use the magic phrase IS NULL instead of = NULL in your SQL queries

You can refer this: http://weblogs.sqlteam.com/markc/archive/2009/06/08/60929.aspx