COALESCE

COALESCE returns the first non-NULL value from a list of arguments. It's the standard SQL way to substitute a default value whenever a column might be NULL.

COALESCE(commission_pct, 0) returns the actual commission if it's set, or 0 if it's NULL — useful in arithmetic, since any expression involving NULL normally evaluates to NULL. Oracle's NVL does the same thing for exactly two arguments; COALESCE is the ANSI-standard, portable version and accepts any number of arguments, evaluated left to right.

Because it's standard SQL, prefer COALESCE over vendor-specific functions like NVL or ISNULL when portability across databases matters.

SELECT first_name, COALESCE(commission_pct, 0) AS commission
FROM employees;