NULLIF and CASE expressions

Use the CASE and NULLIF expressions for conditional expressions in Derby.

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 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.

You do not need to use the CASE expression for avoiding NullPointerExceptions when a nullable column becomes a method receiver.

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

If the value of the instance specified in an instance method invocation is null, the result of the invocation is null (SQL NULL). However, you still might need to use the CASE expression for when a nullable column is a primitive method parameter.