DEV Community

Baldwin Apps
Baldwin Apps

Posted on • Originally published at Medium

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

#sql #database #programming #dataengineering

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, ...)
Enter fullscreen mode Exit fullscreen mode

✅ 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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

(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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Better:

  • keep it numeric: 
COALESCE(age, 0)
Enter fullscreen mode Exit fullscreen mode
  • or keep it text: 
COALESCE(CAST(age AS VARCHAR), 'unknown')
Enter fullscreen mode Exit fullscreen mode

(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.

Top comments (0)