I usually write case statement as (CASE WHEN column= value THEN result) But then I found some person wrote as (CASE column WHEN value THEN result) and the result is different as the code below

select  A.value,

CASE  A.value
  WHEN NULL
  THEN CAST('N' AS VARCHAR(1))
  ELSE CAST( A.value AS VARCHAR(1))
END AS CASE_TYPE_1,

CASE WHEN  A.value IS NULL
  THEN CAST('N' AS VARCHAR(1))
  ELSE CAST( A.value AS VARCHAR(1))
END AS CASE_TYPE_2

from A AS A

result

A.value      CASE_TYPE_1     CASE_TYPE_2 
NULL             NULL             N

I was wondering how the logic of CASE_TYPE_1 and CASE_TYPE_2 differ?

Ps. I wrote and test the script on Azure Databricks

CodePudding user response：

A value can never equal NULL. Your first option will be executed as A.value = null which can never be true. This option can only be used when checking for a specific value, not for checking if IS NULL or not. For example, these two CASE WHEN constructs will produce the identic result:

SELECT A.value,
CASE A.value
  WHEN 1
  THEN CAST('N' AS VARCHAR(1))
  ELSE CAST(A.value AS VARCHAR(1))
END AS CASE_TYPE_1,
CASE WHEN A.value = 1
  THEN CAST('N' AS VARCHAR(1))
  ELSE CAST(A.value AS VARCHAR(1))
END AS CASE_TYPE_2
FROM A;

Using CASE WHEN in order to just replace NULL values is mostly not necessary.

COALESCE will do the same:

SELECT A.value,
CAST(COALESCE(A.value, 'N') AS VARCHAR(1)) AS yourResult
FROM A;

You can verify those things here: db<>fiddle