---
title: "COALESCE"
description: "COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns."
canonical: "https://motherduck.com/glossary/coalesce/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "DuckDB × cognee: Run SQL Analytics Right Beside Your Graph-Native RAG"
    url: "https://motherduck.com/blog/duckdb-cognee-sql-analytics-graph-rag/"
  - title: "What's New in DuckDB 1.5! | MotherDuck"
    url: "https://motherduck.com/videos/whats-new-duckdb-15/"
---

# COALESCE

> COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.

## Overview
`COALESCE(expr1, expr2, ..., exprN)` evaluates its arguments in order and returns the first one that is not `NULL`. If every argument is `NULL`, the result is `NULL`. It's part of standard ANSI SQL and available in essentially every SQL database, including DuckDB.

```sql
SELECT COALESCE(preferred_name, first_name, 'Unknown') AS display_name
FROM users;
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## Common use cases
**Default values.** Replace missing data with a fallback:
```sql
SELECT product_id, COALESCE(discount_pct, 0) AS discount_pct
FROM products;
```

**Merging columns from different sources**, such as picking whichever address field is populated:
```sql
SELECT COALESCE(shipping_address, billing_address) AS effective_address
FROM orders;
```

**Safe arithmetic**, since any arithmetic involving `NULL` produces `NULL`:
```sql
SELECT quantity * COALESCE(unit_price, 0) AS line_total
FROM order_items;
```

## COALESCE vs CASE vs NULLIF
`COALESCE(a, b)` is shorthand for `CASE WHEN a IS NOT NULL THEN a ELSE b END`, extended to any number of arguments. It's the inverse in spirit of `NULLIF`, which turns a specific value *into* `NULL` rather than replacing `NULL` with a value.

## DuckDB notes
DuckDB implements `COALESCE` with short-circuit evaluation -- later arguments aren't evaluated once a non-`NULL` value is found, which matters if a later argument is an expensive expression or subquery. DuckDB also supports the related `IFNULL(a, b)` as a two-argument convenience function equivalent to `COALESCE(a, b)`.

## Type considerations
All arguments to `COALESCE` should share (or be implicitly convertible to) a common type; DuckDB will attempt to find a common supertype across the arguments and cast accordingly, but mixing incompatible types (e.g., a date and a string that isn't a valid date literal) will raise an error.