How to Handle NULL Values in SQL Using COALESCE

Real data is full of gaps. A customer skips the phone number field, a sensor misses a reading, a form is left half-filled. SQL stores these gaps as NULL, not 0 or an empty string. It means “no value is known”.

That one idea explains why NULL behaves strangely, and why you need special tools to handle it.

Finding Missing Values

You can’t test for NULL with =. A comparison like phone = NULL never comes back true, not even for rows where the phone really is empty. Use IS NULL and IS NOT NULL instead.

Here is a members table for a gym.

SELECT name
FROM members
WHERE phone IS NULL;

Output:

Filling Gaps with COALESCE

COALESCE takes a list of values and returns the first one that is not NULL. It is the standard way to put a sensible default in place of a gap, so reports do not show blanks.

SELECT name,
       COALESCE(phone, 'Not provided') AS phone,
       COALESCE(city, 'Unknown')       AS city
FROM members;

Output:

Code Explanation:

• For each row, COALESCE checks the first argument. If it holds a value, that value is returned.
• If the first argument is NULL, the function moves on and returns the fallback text instead.
• The original table is not changed. The substitution happens only in the query result.

COALESCE works with numbers too. Writing COALESCE(discount, 0) treats a missing discount as zero, which keeps your calculations from turning into NULL. MySQL has IFNULL for the same job, but COALESCE works in both.

A Small Trick with NULLIF

NULLIF(a, b) does the opposite. It returns NULL when the two values are equal. It is most useful for avoiding division by zero, as in total / NULLIF(count, 0), which gives NULL instead of an error.