NULLIF and CASE expressions

NULLIF expression syntax

NULLIF ( L, R )

The NULLIF expression is very similar to the CASE expression. For example:

NULLIF(V1,V2)

is equivalent to the following CASE expression:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

CASE expression syntax

You can place a CASE expression anywhere an expression is allowed. It chooses an expression to evaluate based on a boolean test.

CASE 
  WHEN booleanExpression THEN thenExpression 
  [ WHEN booleanExpression THEN thenExpression ]...
  ELSE elseExpression 
END

ThenExpression and elseExpression are both expressions that must be type-compatible. For built-in types, this means that the types must be the same or a built-in broadening conversion must exist between the types.

-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END

-- returns 7
VALUES
   CASE
      WHEN 1 = 2 THEN 3
      WHEN 4 = 5 THEN 6
      ELSE 7
   END