Part 108: Null Handling Functions: COALESCE, IFNULL & NULLIF

Safeguarding queries against unexpected NULL values.

1. COALESCE(val1, val2, ... valN)

Returns the first non-null value in the list:

-- Fallback chain: mobile phone -> home phone -> 'No Phone Provided'
SELECT COALESCE(mobile_phone, home_phone, 'N/A') AS contact_phone
FROM users;

2. NULLIF(a, b): Preventing Division by Zero!

Returns NULL if a == b, otherwise returns a:

-- If total_orders is 0, NULLIF turns it to NULL, preventing crash!
SELECT total_revenue / NULLIF(total_orders, 0) AS average_order_value
FROM store_kpis;

Key Takeaways

• Use COALESCE for fallback defaults.
• Always wrap denominators in NULLIF(col, 0) to prevent fatal division-by-zero crashes!

In Part 109, we explore Type Casting & Conversions: CAST() and CONVERT()!