COALESCE in BigQuery: syntax, examples and common mistakes

What COALESCE is used for

A default for a missing value:

SELECT COALESCE(discount, 0) AS discount
FROM orders;

A fallback across columns. Take the mobile number, or the work number, or the home number, whichever exists first:

SELECT COALESCE(mobile, work_phone, home_phone)
  AS phone
FROM contacts;

Trying several parsers. A date column with mixed formats:

SELECT COALESCE(
  SAFE.PARSE_DATE('%Y-%m-%d', '06/10/2026'),
  SAFE.PARSE_DATE('%d/%m/%Y', '06/10/2026')
);
-- 2026-10-06

The first parser returns NULL for that layout, the second succeeds. See date functions for SAFE.PARSE_DATE.

Keeping a string from disappearing. CONCAT returns NULL if any part is NULL:

SELECT CONCAT(
  'a', COALESCE(CAST(NULL AS STRING), ''), 'b');
-- ab

A safe division with a default:

SELECT COALESCE(SAFE_DIVIDE(10, 0), 0);
-- 0.0

COALESCE vs IFNULL vs NULLIF

IFNULL(a, b) is COALESCE with exactly two arguments. Use whichever reads better; there is no difference in the result.

NULLIF goes the other way: it creates a NULL. That makes the two a useful pair.

An empty string is not NULL

This is the mistake that makes COALESCE look broken:

SELECT
  COALESCE('', 'x'),                         -- ''
  COALESCE(NULLIF('', ''), 'x'),             -- x
  COALESCE(NULLIF(TRIM('  '), ''), 'x');     -- x

An empty string is a value, so COALESCE returns it. Data from forms and spreadsheets is full of empty strings and strings of spaces. Turn them into NULL first with NULLIF(TRIM(col), ''), then apply the fallback.

All arguments must share a type

SELECT COALESCE(amount, 'unknown')
FROM orders;

If amount is a number, BigQuery refuses:

No matching signature for function COALESCE
  Argument types: INT64, STRING

Cast to the type you want out: COALESCE(CAST(amount AS STRING), 'unknown'). Numbers of different kinds do combine: COALESCE(1, 2.5) returns 1.0, a FLOAT64. More on casting in CAST and SAFE_CAST.

Replacing NULL with zero changes an average

Take three rows: 10, NULL, 20.

SELECT
  SUM(x),                 -- 30
  SUM(COALESCE(x, 0)),    -- 30
  AVG(x),                 -- 15.0
  AVG(COALESCE(x, 0)),    -- 10.0
  COUNT(x),               -- 2
  COUNT(*)                -- 3
FROM UNNEST([10, NULL, 20]) AS x;

The sum does not change. The average drops from 15 to 10, because aggregate functions skip NULL and now there is a third value, zero, to count.

Neither is wrong. They answer different questions: “average of the orders we have a value for” and “average over all rows, missing counted as nothing”. Decide which one the metric means before you wrap a column in COALESCE(x, 0). If NULL means “not measured”, zero is a claim you are making about the data.

COALESCE in WHERE and JOIN

COALESCE in a filter or a join condition works, and it has a cost: a function around a column can prevent BigQuery from pruning partitions or using clustering on that column.

-- prefer this
WHERE country = 'US' OR country IS NULL

-- over this
WHERE COALESCE(country, 'US') = 'US'

In a join, ON COALESCE(a.key, '') = COALESCE(b.key, '') matches rows with missing keys to each other. That is occasionally intended and usually a source of duplicated rows.

COALESCE in Google Sheets

Google Sheets has no COALESCE function. For the first non-empty of two cells:

=IF(A2<>"", A2, B2)

For the first non-empty cell in a range:

=INDEX(FILTER(A2:C2, A2:C2<>""), 1, 1)

IFNA and IFERROR look similar and are not the same thing: they replace an error, not an empty cell. The Sheets formulas here are given from the function definitions and were not part of the BigQuery test run.

Mistakes to avoid

• Expecting an empty string to be skipped. It is not NULL.
• Mixing types in the argument list.
• COALESCE(x, 0) before AVG without deciding what a missing value means.
• Defaults that hide a problem. COALESCE(revenue, 0) on a column that should never be NULL turns a broken load into a quiet zero. Count the NULLs first: COUNTIF(revenue IS NULL).

Where the default belongs

A default is a small business rule. “Missing discount means zero” is probably right. “Missing revenue means zero” probably is not. Either way, the rule should be stated once and be visible, because the next person who reads the report cannot see that a zero used to be a NULL.

In OWOX Data Marts the query that applies those defaults is a Data Mart: your SQL, with a description that can say what a missing value means, an owner, and reports that read from it in your warehouse. OWOX does not fill gaps in your data or pick defaults. It keeps the rule you wrote in one place where it can be read.

Google BigQueryGA4 user properties in BigQuery: user_id, user_pseudo_id, first touch and LTVOctober 16, 2026→Google BigQueryBigQuery Partitioned Tables: How to Create and Query ThemOctober 6, 2026→Google BigQueryBigQuery statistical aggregate functions: STDDEV, VARIANCE, CORR and medianOctober 16, 2026→

See all articles →