Questions tagged [case]

The CASE expression is an SQL construction used to return different values depending on specified conditions. There are two forms of the CASE expression. The first is the simple CASE:

CASE search_expr
WHEN compare_expr1 THEN result_expr1
WHEN compare_expr2 THEN result_expr2
ELSE else_expr
END

This will compare search_expr to each compare_expr in order until it finds a condition where search_expr = compar_expr, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

The second form of CASE expression is the searched CASE:

CASE 
WHEN boolean_expr1 THEN result_expr1
WHEN boolean_expr2 THEN result_expr2
ELSE else_expr
END

This will test boolean_expr in order until it finds a condition where the boolean_expr is true, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

There are two short forms for special CASE expressions; COALESCE and NULLIF.

COALESCE(x1, x2, ..., xn) 

is equivalent to:

CASE WHEN x1 IS NOT NULL THEN x1 
     WHEN x2 IS NOT NULL THEN x2
     ...
     ELSE xn
END 

I.e. the COALESCE expression returns the value of the first non-null operand, found by working from left to right, or null if all the operands equal null.

NULLIF(x1, x2) 

is equivalent to

CASE WHEN x1 = x2 THEN NULL ELSE x1 END

I.e. if the operands are equal, NULLIF returns null, otherwise the value of the first operand.