SQL Server Compare to NULL

Question

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  

Nim

How about WHERE isnull(ReceivedBy,'ReceivedBy is null')') != isnull(PreviousReceivedBy,'PreviousReceivedBy is null')... – Fajardo

In Hackerman's answer above, if both values are NULL, isnull(ReceivedBy,'ReceivedBy is null')') != isnull(PreviousReceivedBy,'PreviousReceivedBy is null') would be TRUE when it should be FALSE. (Could be fixed by replacing both literals with another, equal value.) – Phillipp

Answer 1

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

Biramous

Answer 2

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 '#' :)

Trincomalee

I would point out that the value could be literally anything, and the null check should be something outside of the realm of possibility. I seem to remember a case with an airline company that did this exact thing, and then met someone whose legitimate family name was Null. The airline company lost ;) – Stridulate

You can also use ISNULL instead of COALESCE (A bit shorter and easier to type :p) – Mannerheim

If the expressions are of type UNIQUEIDENTIFIER then it will result in "Conversion failed when converting from a character string to uniqueidentifier." because those alternative strings cannot be converted to uids for comparison. – Jennine

Answer 3

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

assuming the columns never have negative values

Financier

Answer 4

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

Stridulate

This does not seem to work when the values are the same. – Nim

@Nim I wasn’t aware you needed that condition. Are you only looking for things where they’re both not null, then? – Stridulate

My original statement was WHERE ReceivedBy != PreviousReceivedBy to catch only scenarios where they are different. So I need it to evaluate to FALSE if the values are the same. The first half will be FALSE, but if they are both a non-null value, then the second half will be TRUE. FALSE OR TRUE = TRUE. So my expression would then catch things that are the same, and I need them excluded. – Nim

See my updated question for the examples of what I am hoping for. – Nim

@Nim I see - updated the OR to an AND for that logic. This would require one of the values to be non-null and for them not to be equal. I can’t test this at the moment, but this should be correct - please let me know if it isn’t. NULL cannot equal anything, so the first check would pass if one is null, and two NULL values would be filtered out by the second. If neither are NULL, the comparison will function as normal. – Stridulate

I think there is a flaw here. I recently learned that a comparison to null always equals UNKNOWN. which cannot be "NOT"ed to TRUE. https://mcmap.net/q/1015989/-confusing-null-compares-combined-with-not I think I will have to just go with my expanded logic. Thank you for taking the time to look into this. – Nim

Answer 5

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

Chiffonier

This is a game changer. What a pity I'm running an older version of SQL Server – Bamberger

Answer 6

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)

Extradition

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

Citadel

You can use backticks (`) to highlight inline code. This will make it easier to understand the answer – Benefit

This does not work, since declare @a int = null, @b int = 123; select nullif(@a, @b); returns NULL (false) even though @a != @b (it should be true). – Phillipp

Try this: (NULLIF(@a, @b) IS NOT NULL) OR (NULLIF(@b, @a) IS NOT NULL) – Citadel