Lesson 22: NULL Handling: IS NULL, COALESCE, NULLIF

NULL means 'unknown/absent', not zero or empty string — and it behaves specially in comparisons, which trips up almost everyone at first.

Real-time example

A report must treat customers with no phone number as 'Not provided' instead of showing a blank or breaking a comparison.

SELECT name, COALESCE(phone, 'Not provided') AS phone
FROM customers
WHERE phone IS NULL OR phone = '';

SELECT NULLIF(discount, 0) FROM orders; -- turns 0 into NULL

What's happening

amount = NULL never matches anything, even another NULL — that's why you must use IS NULL / IS NOT NULL, not = NULL.

COALESCE returns the first non-null argument — the standard tool for supplying a default value in place of NULL.