COALESCE, NULLIF and ISNULL
Substituting defaults and the difference between COALESCE and vendor-specific functions.
Substituting Values for NULL
Now that you can detect NULL, the next interview skill is replacing it with a sensible default. The portable, standard tool for this is COALESCE.
Alongside it you will meet NULLIF, which goes the other direction by turning a specific value into NULL, and the vendor functions ISNULL (SQL Server) and IFNULL (MySQL) that candidates often confuse with COALESCE.
Knowing exactly how each differs, especially in argument count and return type, is a frequent screening question.
COALESCE Basics
COALESCE takes any number of arguments and returns the first non-NULL one, scanning left to right. If all arguments are NULL, it returns NULL.
It is ANSI standard and works on every major database, which is why it should be your default answer. Use it to supply fallbacks for display, calculation, or grouping.
-- Show 0 instead of NULL for missing bonuses
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;
-- Multiple fallbacks, first non-NULL wins
SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;