COALESCE in SQL: the “Don’t Let NULL Win” Function You’ll Use Everywhere

Baldwin Apps — Wed, 18 Feb 2026 11:07:46 +0000

Cross-posted from Medium. If NULL has ever “blanked out” a result you know should exist, COALESCE is the fix you’ll use forever.

If you’ve ever looked at a query result and thought:

“Why is this blank? I know there’s data here…”

…it was probably NULL doing what NULL does.

Some databases will happily let a missing value sneak through your calculations, concatenations, and reports — then you’re left debugging a spreadsheet like it’s a crime scene.

Enter COALESCE: one of the simplest functions in SQL, and also one of the most useful.

What COALESCE does (in plain English)

COALESCE returns the first non-NULL value from a list of expressions.

Think of it like a fallback chain:

If this value is NULL, try the next one
If that’s NULL too, try the next one
Keep going…
If they’re all NULL, you get NULL

The basic pattern

COALESCE(value1, value2, value3, ...)

✅ Returns the first non-NULL value.

Quick example: “Give me a name… any name”

Let’s say you have a users table and people can have a display name, a full name, or (worst case) nothing.

SELECT
  COALESCE(display_name, full_name, 'Anonymous') AS shown_name
FROM users;

This reads like:

Use display_name if it exists
Otherwise use full_name
Otherwise label them 'Anonymous'

If you’re building apps, dashboards, or any user-facing output, this is basically required.

COALESCE vs NULL: why it matters

NULL isn’t “empty” or “zero” or “blank”.

NULL means: unknown / missing / not provided.

And SQL treats NULL like a wildcard ghost.

NULL breaks math

SELECT 10 + NULL;   -- result: NULL

So if you’re doing totals, averages, costs, scoring, etc., one NULL can nuke your result.

Fix it with COALESCE

SELECT 10 + COALESCE(NULL, 0);  -- result: 10

Real-world use cases you’ll hit constantly

1) Safe totals (avoid NULL turning everything into NULL)

SELECT
  order_id,
  COALESCE(subtotal, 0)
  + COALESCE(tax, 0)
  + COALESCE(shipping, 0) AS total
FROM orders;

This is reporting-grade SQL: stable, predictable, and less likely to make you question reality at 1 a.m.

2) Text concatenation without “NULL poisoning”

Many SQL dialects will produce NULL if you concatenate text with NULL.

SELECT
  COALESCE(first_name, '')
  || ' '
  || COALESCE(last_name, '') AS full_name
FROM customers;

(Depending on your database you may use CONCAT() instead of ||, but the COALESCE idea stays the same.)

3) “Prefer this column, fallback to that one”

Classic: billing address might be missing so you fall back to shipping:

SELECT
  customer_id,
  COALESCE(billing_state, shipping_state) AS state
FROM customer_addresses;

Common gotcha: type mismatches

COALESCE returns a single value, and SQL tries to make all the arguments compatible.

So avoid mixing types like:

COALESCE(age, 'unknown')   -- might error or auto-cast in weird ways

Better:

keep it numeric:

COALESCE(age, 0)

or keep it text:

COALESCE(CAST(age AS VARCHAR), 'unknown')

(Casting syntax varies by database.)

Your turn

Where are NULLs messing with your life right now?

totals?
string output?
missing user fields?
inconsistent imports?

Drop your scenario in the comments — if it’s a common pain point, I’ll write the next article as a fix.

🫧 SQL Bubble Pop — a quick, game-style SQL trainer designed to help you build instinct for joins, views, CTEs, and query logic in short daily sessions.

DEV Community: Baldwin Apps