Explain NVL, NVL2, COALESCE and NULLIF in PL/SQL

How it works

NVL(expr, replacement) substitutes a value only when expr is NULL. NVL2(expr, value_if_not_null, value_if_null) branches on whether expr is NULL at all, returning one of two different expressions either way. COALESCE(expr1, expr2, ...) is the ANSI-standard, more general form of NVL — it returns the first non-NULL expression from any number of arguments, not just two. NULLIF(expr1, expr2) returns NULL if the two are equal, otherwise expr1 — handy for turning a sentinel value like 0 into a real NULL before an aggregate.

SELECT
  NVL(commission_pct, 0)                                    AS safe_commission,
  NVL2(commission_pct, 'Has commission', 'No commission')    AS commission_flag,
  COALESCE(commission_pct, bonus_pct, 0)                     AS first_available_rate,
  NULLIF(discount_pct, 0)                                    AS discount_or_null
FROM employees;

Related Questions