Shape / columns · All operations

Take the first value that is present

A variant of Fill in missing values

Return, row by row, the first non-null value across several columns.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Take the first non-null value across several columns

SQL
CREATE TEMP TABLE contacts AS SELECT * FROM (VALUES
  (1, 'mobile-1', 'home-1', 'work-1'), (2, NULL, 'home-2', NULL), (3, NULL, NULL, 'work-3'), (4, NULL, NULL, NULL), (5, '', 'home-5', NULL)
) t(id, mobile, home, work);
CREATE TEMP TABLE result AS
SELECT id, coalesce(mobile, home, work) AS phone, coalesce(mobile, home, work, 'none') AS phone_or_none FROM contacts;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(phone ORDER BY id) FROM result) = ['mobile-1', 'home-2', 'work-3', NULL, ''] AND (SELECT list(phone_or_none ORDER BY id) FROM result) = ['mobile-1', 'home-2', 'work-3', 'none', '']) THEN 'as expected' ELSE error('the first non-null phone was not taken row by row, or an all-null row did not stay null, or an empty string was skipped') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the first non-null phone was not taken row by row, or an all-null row did not stay null, or an empty string was skipped”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
  • Conditional Functionsread 2026-09-27

    7 functions, 1 of them aliases: coalesce, nullif, greatest, least, nvl, …

Apache Hop (1)
  • Coalesceread 2026-09-27

    Return the first non-null value across a list of fields

Duckle (1)
Ibis (1)
  • Coalesceread 2026-09-27

    Take the first non-null value from a list of expressions

KNIME Analytics Platform (1)
  • Column Mergerread 2026-09-27

    Coalesce two columns, taking the secondary value where the primary is missing

Polars (1)
  • coalesceread 2026-09-27

    First non-null value across several columns, row by row

Tableau Prep (1)
  • Merge Fieldsread 2026-09-27

    Merge fields into a target field: nulls in the target take the other fields' values

Background

In SQL, the COALESCE function accepts a list of values and returns the first value in the list that is not null.[1] In PostgreSQL, COALESCE returns null only if all of its arguments are null.[2] In SQL, COALESCE is shorthand for a CASE expression that tests each argument with IS NOT NULL in turn.[1]

When to use it

In PostgreSQL, COALESCE is often used to substitute a default value for null values when data is retrieved for display, with a literal such as '(none)' as its last argument.[2] List the arguments from the most trusted source to the least, and end with a literal only when a placeholder serves the reader better than a null. (Meridian's view) In PostgreSQL, the arguments of COALESCE must all be convertible to a common data type, which becomes the type of the result.[2] Cast mixed columns, such as a numeric code and a text code, to one type yourself before combining them. (Meridian's view)

In SQL, null is defined as different from an empty string, which is an actual value.[1] Apache Hop's Coalesce transform has an option to consider an empty string as null.[3] In an Oracle database, NULL and the empty string are considered the same thing.[1]

The method

In PostgreSQL, COALESCE evaluates only the arguments needed to determine the result, so arguments to the right of the first non-null argument are not evaluated.[2] Oracle's NVL function takes two parameters, and in most implementations COALESCE evaluates its parameters until it reaches the first non-NULL one, while NVL evaluates all of them.[1] A COALESCE of any length can be written as nested NVL calls, each taking the next value as its fallback.[1] DuckDB's coalesce accepts one or more parameters, each of which can be a column, a literal value or a function result.[4] DuckDB's ifnull is a two-argument version of coalesce.[5][4]

Compared with the alternatives

DuckDB's any_value aggregate returns the first non-null value of one argument across a group's rows, and its result is affected by ordering.[6]

Sources

  1. [1]Null (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 9.18. Conditional Expressionsdocumentation
  3. [3]Coalesce | Apache Hopdocumentation
  4. [4]Utility Functions – DuckDBdocumentation
  5. [5]NULL Values – DuckDBdocumentation
  6. [6]Aggregate Functions – DuckDBdocumentation