Not equal <> != operator on NULL
Not equal <> != operator on NULL
问题
Could someone please explain the following behavior in SQL?
SELECT * 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)
回答1
<>
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.
回答2
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.
回答3
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...
SQL Server Compare to NULL
I encountered the same problem with you when taking comparison with nullable value, NULL
always returns unknown
as far away of our desired only between TRUE
or FALSE
I ended up with declare a Scalar-valued functions with these logics like other SQL(s) dealing with null as
Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs:
a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b
Which a IS NOT DISTINCT FROM b
could be rewritten as
(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)
I use sql_variant for these basic parameters: int, datetime, varchar,...
create function IsEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end
create function IsNotEqual(
@a sql_variant,
@b sql_variant
)
returns bit
as
begin
return 1-dbo.IsEqual(@a,@b);
end
To use
select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1) _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date
For your cases
select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null
How to compare values which may both be null in T-SQL
回答1
Along the same lines as @Eric's answer, but without using a 'NULL'
symbol.
(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)
This will be true only if both values are non-NULL
, and equal each other, or both values are NULL
回答2
Use ISNULL
:
ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')
You can change 'NULL'
to something like 'All Values'
if it makes more sense to do so.
It should be noted that with two arguments, ISNULL
works the same as COALESCE
, which you could use if you have a few values to test (i.e.-COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')
). COALESCE
also returns after the first non-null, which means it's (marginally) faster if you expect MY_FIELD1 to be blank. However, I find ISNULL
much more readable, so that's why I used it, here.