CODEWITHSUNDEEP
sqlsql-servert-sqlnull
Vaccano
Vaccano

Reputation: 82341

SQL Server Compare to NULL

I have a lot of comparisons that I need to make between a value and its previous value.

For Example: ReceivedBy and PreviousReceivedBy.

I started with:

WHERE ReceivedBy != PreviousReceivedBy

But if either value is null then this returns false, (when I really need it to be true). So I updated it to look like this:

WHERE ReceivedBy != PreviousReceivedBy
      OR (ReceivedBy IS NULL AND PreviousReceivedBy IS NOT NULL)
      OR (ReceivedBy IS NOT NULL AND PreviousReceivedBy IS NULL)

This works fine, but I have a large list of fields that need to be compared. I would like to find a way to make this comparison with less code (without turning off ANSI_NULLS).

Obviously if there is no other way, then I will just put in all 3 lines for the comparison.

UPDATE:

As an example, here is what I am hoping for

ReceivedBy = 123  
PreviousReceivedBy = 123  
Result = FALSE  

ReceivedBy = 5  
PreviousReceivedBy = 123  
Result = TRUE  

ReceivedBy = NULL  
PreviousReceivedBy = 123  
Result = TRUE

ReceivedBy = 123  
PreviousReceivedBy = NULL  
Result = TRUE  

ReceivedBy = NULL  
PreviousReceivedBy = NULL  
Result = FALSE

Upvotes: 11

Views: 20915

Answers (7)

user12116884
user12116884

Reputation: 

DECLARE @phantom_not_null int = -1

DECLARE @Tests TABLE (Expected int, Actual int)

INSERT @Tests VALUES (NULL, NULL), (NULL, 0), (0, NULL), (0, 0), (0, 1)

SELECT * FROM @Tests 
WHERE ISNULL(Expected, @phantom_not_null) != ISNULL(Actual, @phantom_not_null)

Results here

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

if either value is null then this returns false

It actually returns "unknown" rather than "false" (SQL uses three valued logic). But in a WHERE clause the combined predicates must evaluate to "true" for the row to be returned so the effect here is much the same.

From SQL Server 2022 you can use

WHERE  ReceivedBy IS DISTINCT FROM PreviousReceivedBy

Upvotes: 3

Garm
Garm

Reputation: 7

(NULLIF(@a, @b) IS NOT NULL) OR (NULLIF(@b, @a) IS NOT NULL) means "@a != @b even if one of them or both are null.

Upvotes: -1

Андрей Чеботюк
Андрей Чеботюк

Reputation: 41

WHERE ISNULL(ReceivedBy, -1) != ISNULL(PreviousReceivedBy, -1)

assuming the columns never have negative values

Upvotes: 4

Alfred Luu
Alfred Luu

Reputation: 1983

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

Result

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

enter image description here

Upvotes: 4

Michał Turczyn
Michał Turczyn

Reputation: 37367

If both columns are varchars, I'd go with something like this:

coalesce(ReceivedBy, 'NULL') != coalesce(PreviousReceivedBy, 'NULL')

If they are integers, I'd put some values greatly below zero (to distinctly represent null value) instead of 'NULL'.

From names of columns I assume it has to be wether string value or integer value :)

UPDATE

As @Siyual pointed out, replacement string should be "out of the realm of possibility", you should replace 'NULL' above with some non-alphabetical character, as '#' :)

Upvotes: 4

Siyual
Siyual

Reputation: 16917

Another method without munging the data would be to use COALESCE

Where ReceivedBy != PreviousReceivedBy
And Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null

NULL cannot equal anything, not even another NULL, so if any of the values are NULL, ReceivedBy != PreviousReceivedBy will evaluate as true.

Secondly, if both of the values are NULL, the Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null will evaluate as false, forcing those to be filtered.

If neither are NULL, the first condition would fail if they are equal.

Admittedly, it’s not saving too much code, but it is an improvement.

This can be easily grouped in parenthesis and copy/pasta’d for all remaining fields you need to check.

Where (ReceivedBy != PreviousReceivedBy And Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null)
And[Or] (Foo != Bar And Coalesce(Foo, Bar) Is Not Null)
...

Upvotes: 3

Related Questions