NULL = NULL

Question

Post reply

NULL = NULL

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
July 1, 2020 at 7:12 am
Go to Answer
#3766615

Hi clever people!
given the following:
SELECT CASE WHEN NULL = NULL THEN 'No' ELSE 'Yes' END AS Result
Result would be no because one null value cannot be equal to another because null means 'UNKNOWN'.
1. What if anything could influence this query to produce a different result?
2. Provide a query to behave correctly.

Kind regards

DesNorton

SSC-Insane

Points: 24416

July 1, 2020 at 8:38 am

Answer

#3766629

In order to compare NULL values, you need to convert them to a known value.

Below is how I would do it, with 3 different data types

DECLARE @Var1 varchar(10) = NULL;
DECLARE @Var2 varchar(10) = NULL;

SELECT [Direct_Compare] = CASE WHEN @Var1 = @Var2 THEN 'Match' ELSE 'No Match' END
, [IsNull_Compare] = CASE WHEN ISNULL(@Var1, '') = ISNULL(@Var2, '') THEN 'Match' ELSE 'No Match' END;
GO

DECLARE @Var1 int = NULL;
DECLARE @Var2 int = NULL;

SELECT [Direct_Compare] = CASE WHEN @Var1 = @Var2 THEN 'Match' ELSE 'No Match' END
, [IsNull_Compare] = CASE WHEN ISNULL(@Var1, 0) = ISNULL(@Var2, 0) THEN 'Match' ELSE 'No Match' END;
GO

DECLARE @Var1 datetime = NULL;
DECLARE @Var2 datetime = NULL;

SELECT [Direct_Compare] = CASE WHEN @Var1 = @Var2 THEN 'Match' ELSE 'No Match' END
, [IsNull_Compare] = CASE WHEN ISNULL(@Var1, '1900-01-01') = ISNULL(@Var2, '1900-01-01') THEN 'Match' ELSE 'No Match' END;
GO

How to post data/code on a forum to get the best help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Here is an alternative which avoids the use of IsNull() and does not require 'conversion to a known value':

DECLARE @Var1 DATETIME = NULL;
DECLARE @Var2 DATETIME = NULL;

SELECT Direct_Compare = CASE
                            WHEN @Var1 = @Var2 THEN
                                'Match'
                            ELSE
                                'No Match'
                        END
      ,Is_Null_Compare = CASE
                             WHEN @Var1 IS NULL
                                  AND @Var2 IS NULL THEN
                                 'Match'
                             ELSE
                                 'No Match'
                         END;

This reply was modified 4 years, 6 months ago by Phil Parkin.
This reply was modified 4 years, 6 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 2

I, personally, tend to go down this route:

CASE WHEN Col1 = Col2 OR (Col1 IS NULL AND Col2 IS NULL) THEN 'Yes'
                                                         ELSE 'No'
END AS Matches

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 3

This is the standard SQL 'IS DISTINCT FROM' operator, not current implemented in T-SQL (yet!)

Interesting article below mentions it is implemented in the query processor

https://www.sql.kiwi/2011/06/undocumented-query-plans-equality-comparisons.html

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ratbak Ten Centuries Points: 1382 More actions · Answer 4

"SELECT CASE WHEN NULL = NULL THEN 'No' ELSE 'Yes' END AS Result

Result would be no because one null value cannot be equal to another because null means 'UNKNOWN'."

Perhaps just a typo, but the result is "Yes", not "No", precisely because of the argument you stated well.