COALESCE is a SQL function that takes a list of values and returns the first one that isn’t null. It’s the standard way to say “use this column, and if it’s empty, fall back to that one” — a small function that shows up constantly in real-world queries, because real-world data is full of nulls.
How it works
COALESCE accepts any number of arguments and evaluates them left to right, stopping at the first non-null value. If every argument is null, it returns null. A common use is picking the best available version of a value:
SELECT
order_id,
COALESCE(shipping_address, billing_address, 'Address missing') AS delivery_address
FROM orders;
Here each order gets its shipping address if one exists, the billing address if not, and a placeholder string if both are null. The same trick handles defaults in calculations — COALESCE(discount, 0) keeps a null discount from turning an entire arithmetic expression null, which is exactly what happens if you write price - discount and discount is null.
The arguments should share a compatible data type. Most databases will complain (or quietly cast) if you mix, say, numbers and text, so it’s worth being explicit when the types differ.
COALESCE vs. its dialect cousins
COALESCE is part of the SQL standard, so it works virtually everywhere. Many databases also ship an older, two-argument shortcut that does the same job: Oracle has NVL, MySQL has IFNULL, and SQL Server has ISNULL. They’re fine, but they’re not portable, and most only accept two arguments. If you’re writing queries that might move between databases — or just want one habit that works everywhere — COALESCE is the safer default.
One thing COALESCE doesn’t do is treat empty strings as missing. An empty string '' is a value, not a null, so COALESCE(name, 'Unknown') won’t replace it. If your data mixes both, you’ll need something like NULLIF(name, '') inside the COALESCE to convert empty strings to nulls first.
Coalesce in Metabase
You don’t need to write SQL to use this. Metabase’s query builder includes coalesce as a custom expression function, so you can create a custom column like coalesce([Shipping Address], [Billing Address]) directly in the notebook editor, and Metabase translates it to the right SQL for your database. In the native editor you can of course write COALESCE yourself — it tends to appear anywhere a report needs clean defaults, like replacing null revenue with zero before summing, or picking a display name from several partially-filled fields.
Related terms
Further reading
Put it to work
- SQL cheat sheet — Cheat sheet
- Debugging SQL logic — Tutorial
