COALESCE and NULLIF

Objectives

By the end of this lesson, you should be able to:

• Replace a NULL value with a fallback using COALESCE()
• Turn a specific value into NULL using NULLIF()
• Use NULLIF() to guard against division by zero

💡 Why this matters: A NULL department showing up as a blank cell in a report is confusing, COALESCE() fills it with something readable. Occasionally the opposite is needed too, treating a specific ordinary value (like 0) as if it were missing, which is what NULLIF() does.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier lessons.

COALESCE()

SELECT first_name, COALESCE(department, 'Unassigned') AS department
FROM employees
WHERE first_name = 'Devon';

 first_name | department
------------+-------------
 Devon      | Unassigned

COALESCE(value, fallback) returns value if it isn’t NULL, otherwise it returns fallback. Devon’s department is NULL (no department assigned), so COALESCE substitutes 'Unassigned' instead. COALESCE() actually accepts any number of arguments, not just two, returning the first one that isn’t NULL, useful for a chain of fallbacks (COALESCE(preferred_name, first_name, 'Unknown')).

NULLIF()

SELECT NULLIF(10, 10) AS same, NULLIF(10, 5) AS different;

 same | different
------+------------
      |         10

NULLIF(a, b) returns NULL if a equals b, otherwise it returns a unchanged. NULLIF(10, 10) becomes NULL since both arguments match, NULLIF(10, 5) stays 10 since they don’t. This is, in a sense, the reverse of COALESCE, instead of replacing NULL with a real value, it replaces a specific real value with NULL.

NULLIF as a Division-by-Zero Guard

SELECT 10 / NULLIF(0, 0) AS safe_div;

 safe_div
----------

Dividing by zero normally raises a hard error in PostgreSQL. NULLIF(0, 0) turns the divisor into NULL whenever it would have been 0, and dividing by NULL returns NULL (Module 6) instead of erroring. 10 / NULLIF(some_count, 0) is a common real pattern, computing a rate or average that would otherwise crash on an empty group.

Try It

1. Write a query showing every employee’s department, replacing NULL with 'No Department', using COALESCE.
2. Write a query computing salary / NULLIF(0, 0) and confirm it returns NULL instead of an error.
3. Write a query using COALESCE with three fallback values (a literal NULL, then another literal NULL, then a real value), and confirm it returns the first non-NULL one.
4. Explain, in your own words, a real reporting scenario where NULLIF guarding a division makes more sense than just checking WHERE count > 0 beforehand.

Recap

• COALESCE(value, fallback, ...) returns the first non-NULL argument, commonly used to display a default instead of a blank NULL.
• NULLIF(a, b) returns NULL if a equals b, otherwise returns a, the reverse operation from COALESCE.
• NULLIF combined with division is a standard guard against dividing by zero, since PostgreSQL returns NULL (not an error) for value / NULL.

Next lesson: EXISTS, ANY, and ALL, existence and comparison checks against a subquery.