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

How about `WHERE isnull(ReceivedBy,'ReceivedBy is null')') != isnull(PreviousReceivedBy,'PreviousReceivedBy is null')`... — Hackerman, Jun 18 '18 at 20:45
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.) — Rob at TVSeries.com, Aug 25 '21 at 01:57

Michał Turczyn · Answer 1 · 2018-06-19T05:09:09.940

4

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

edited Jun 19 '18 at 05:09

answered Jun 18 '18 at 20:43

Michał Turczyn

32,028
14
47
69

5

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 ;) – Siyual Jun 18 '18 at 20:59
You can also use ISNULL instead of COALESCE (A bit shorter and easier to type :p) – Aximili Feb 24 '20 at 04:48
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. – djk Jul 05 '20 at 18:22

Siyual · Answer 2 · 2018-06-18T23:09:56.077

3

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

edited Jun 18 '18 at 23:09

answered Jun 18 '18 at 20:55

Siyual

16,415
8
44
58

This does not seem to work when the values are the same. – Vaccano Jun 18 '18 at 22:39
@Vaccano I wasn’t aware you needed that condition. Are you only looking for things where they’re both not null, then? – Siyual Jun 18 '18 at 22:44
1

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. – Vaccano Jun 18 '18 at 22:56
See my updated question for the examples of what I am hoping for. – Vaccano Jun 18 '18 at 22:59
@Vaccano 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. – Siyual Jun 18 '18 at 23:03
1

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://stackoverflow.com/a/50919888/16241 I think I will have to just go with my expanded logic. **Thank you for taking the time to look into this.** – Vaccano Jun 19 '18 at 14:09

score 3 · Accepted Answer · answered May 07 '20 at 04:44

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

score 3 · Answer 4 · answered Mar 31 '21 at 07:49

3

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

assuming the columns never have negative values

answered Mar 31 '21 at 07:49

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

31
1

score 1 · Answer 5 · answered Jul 28 '22 at 01:14

1

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

answered Jul 28 '22 at 01:14

Martin Smith

438,706
87
741
845

1

This is a game changer. What a pity I'm running an older version of SQL Server – iruvar Feb 08 '23 at 17:43

score 0 · Answer 6 · 2023-04-27T11:36:56.437

0

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)

edited Apr 27 '23 at 11:36

answered Apr 27 '23 at 11:31

Garm · Answer 7 · 2021-08-27T11:41:28.327

-1

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

edited Aug 27 '21 at 11:41

answered Sep 24 '20 at 08:35

Garm

7
2

You can use backticks (`) to highlight inline code. This will make it easier to understand the answer – Exelian Sep 24 '20 at 09:05
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). – Rob at TVSeries.com Aug 25 '21 at 01:53
Try this: `(NULLIF(@a, @b) IS NOT NULL) OR (NULLIF(@b, @a) IS NOT NULL)` – Garm Aug 27 '21 at 11:36

SQL Server Compare to NULL

7 Answers7

Linked