COALESCE and NULLIF in SQL

COALESCE gives you a fallback when a column is NULL. NULLIF does the opposite: it converts a specific value into NULL when you want SQL to treat it as missing.

You're building a product revenue report. Some products haven't had any sales yet, so the revenue column is NULL for those rows. Aggregate functions like SUM and AVG skip NULL values entirely, which is often fine for calculations. But when you display totals in a report, a NULL looks like a gap in the data, and you'd rather show zero. COALESCE is what you reach for.

COALESCE accepts any number of arguments and returns the first one that is not NULL:

SQL checks each row's revenue value. If it is not NULL, that value comes through. If it is NULL, SQL returns 0 instead. The output column is always filled.

You can chain as many fallbacks as you need:

SQL works through the list left to right, and the first non-NULL value wins. This is useful when a table has multiple columns that might carry the same piece of information and you want the best available one:

If nickname exists, use it. If not, try first_name. If that too is NULL, return 'Unknown'. Order matters: COALESCE(a, b) and COALESCE(b, a) produce different results whenever both are non-NULL, because the leftmost one always wins.

One common application: wrapping an aggregate. SUM returns NULL when every row in a group is NULL. Wrapping it gives you a usable zero: COALESCE(SUM(revenue), 0). A NULL total and a zero total mean different things. NULL means no data existed for that group. Zero means data existed and summed to nothing. Replacing one with the other is a deliberate presentational choice, not a free cleanup.

NULLIF takes exactly two arguments and returns NULL if they are equal, or the first argument if they are not:

If status is 'N/A', NULLIF converts it to NULL. If status is anything else, it passes through unchanged. This is useful when a column holds a placeholder string standing in for a missing value — converting it to a true NULL lets NULL-aware logic handle it correctly.

The other classic use is protecting against division by zero:

If num_orders is zero, NULLIF returns NULL, and dividing by NULL produces NULL instead of an error. If num_orders is anything else, the division runs normally.

The one thing that trips people up: NULLIF only tests equality between its two arguments.

You cannot use it to suppress a value based on a range or a more complex condition. For anything beyond simple equality, use CASE WHEN:

Both COALESCE and NULLIF are shorthand for CASE WHEN expressions. COALESCE(a, b) is equivalent to CASE WHEN a IS NOT NULL THEN a ELSE b END. They follow the same NULL rules as CASE WHEN, which is why they handle NULL correctly where a naive = check would not.