IF, NULLIF, and COALESCE Functions
IF, NULLIF, and COALESCE are derivative functions of the case expression and can be defined in terms of the case expression.
IF
Returns the second parameter if the first evaluates as true; otherwise it returns any third, if present. The first expression must be a predicate.
It can be represented as follows:
IF(expr1, expr2)
is the same as:
CASE WHEN expr1 THEN expr2 ELSE NULL END
and
IF(expr1, expr2, expr3)
is the same as:
CASE WHEN expr1 THEN expr2 ELSE expr3 END
Example: IF (a = 1, b IS FALSE)
NULLIF
Returns the null value if its two parameters are equal; otherwise it returns the first parameter value. It can be represented as follows:
NULLIF(expr1, expr2)
is the same as:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
COALESCE
Returns the first non-null value of an arbitrary list of parameters. It can be represented as follows:
COALESCE(expr1, expr2)
is the same as:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
and
COALESCE(expr1, expr2, ..., exprn)
is the same as:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE(expr2, ..., exprn)