Case When not check NULL value as defined

Lora 200 Reputation points
2024-01-22T07:31:52.0733333+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,252 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 19,986 Reputation points Microsoft Vendor
    2024-01-22T07:42:11.19+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 38,861 Reputation points
    2024-01-22T07:42:06.81+00:00

    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)

    0 comments No comments