Case When not check NULL value as defined

Question

Here is my query using Case when,

DECLARE @tbl TABLE(ID INT, Col1 DATETIME, Col2 INT)
INSERT INTO @tbl VALUES
(1,'2024-01-22',101),(2,'2024-01-21',102),(3,'2024-01-20',NULL)

SELECT ID,Col1,Col2
      ,CASE Col2 WHEN NULL THEN 'No Value'
                 WHEN 101 THEN 'AAA'
				 WHEN 102 THEN 'BBB'
				 ELSE 'CCC' END AS Col3
FROM @tbl

Why the NULL value is defined as CCC instead of No value?

Answer 1

Hi @Lora CASE Col2 WHEN NULL THEN 'No Value' equals to CASE WHEN Col2 = NULL THEN 'No Value'

NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal.

See this doc: NULL and UNKNOWN

Try this modification:

SELECT ID,Col1,Col2
      ,CASE WHEN Col2 IS NULL THEN 'No Value'
                 WHEN Col2 = 101 THEN 'AAA'
				 WHEN Col2 = 102 THEN 'BBB'
				 ELSE 'CCC' END AS Col3
FROM @tbl

Best regards, Cosmog Hong

Answer 2

Comparing on NULL results always in "Unknown" and so in this case as false, and the ELSE part is taken. See NULL and UNKNOWN (Transact-SQL)