COALESCE returns the first non-NULL value from a list. Essential for handling missing data.

Basic COALESCE

SELECT COALESCE(phone, 'No phone') FROM customers;

Multiple Fallbacks

SELECT COALESCE(mobile, home_phone, work_phone, 'No contact') as phone

FROM customers;

COALESCE in Calculations

SELECT product_name, price * COALESCE(discount, 0) as discount_amount

FROM products;

COALESCE with Aggregates

SELECT customer_id, COALESCE(SUM(total), 0) as total_spent

FROM orders

GROUP BY customer_id;

COALESCE in JOINs

SELECT

c.name,

COALESCE(o.total, 0) as last_order_total

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id;

COALESCE vs IFNULL

-- COALESCE (standard SQL, multiple arguments)

SELECT COALESCE(a, b, c, 'default');

-- IFNULL (MySQL only, two arguments)

SELECT IFNULL(a, 'default');

Generate COALESCE Queries

AI2sql handles NULL values automatically.