Q36: How do COALESCE() and NULLIF() functions work?
COALESCE(val1, val2, ...): Evaluates arguments in order and returns the first non-null value in the parameter list.NULLIF(exp1, exp2): Compares two expressions. ReturnsNULLifexp1 = exp2; otherwise returnsexp1.
Preventing Division-by-Zero using NULLIF and COALESCE
-- Prevents runtime divide-by-zero errors gracefully
SELECT
total_revenue,
total_units,
total_revenue / NULLIF(total_units, 0) AS average_unit_price,
COALESCE(total_revenue / NULLIF(total_units, 0), 0.00) AS safe_unit_price
FROM product_sales;💡 NULLIF converts total_units = 0 to NULL, preventing division-by-zero runtime exceptions. COALESCE then converts resulting NULL values back to 0.00.
Key Takeaways
- COALESCE returns the first non-null argument.
- NULLIF prevents division-by-zero exceptions by returning NULL when arguments match.