---
title: "COALESCE in BigQuery: Syntax, Examples, Common Mistakes"
canonical: "https://www.owox.com/blog/articles/coalesce-operator-bigquery"
updated: "2026-10-06"
---

# COALESCE in BigQuery: syntax, examples and common mistakes

COALESCE returns the first value in a list that is not NULL. This guide shows it on defaults, fallback columns, empty strings and mixed date formats, compares it with IFNULL and NULLIF, and explains why replacing NULL with zero changes an average.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 4 min read

![COALESCE in BigQuery: syntax, examples and common mistakes](https://cdn.owox.com/www/webflow/696a5b829002630b6077707c_COALESCE-OPERATOR.png/public)

`COALESCE(a, b, c, ...)` returns **the first argument that is not `NULL`**. If all of them are `NULL`, it returns `NULL`.

```
SELECT
  COALESCE(NULL, NULL, 'c'),  -- c
  COALESCE(NULL, 0),          -- 0
  COALESCE(NULL, NULL);       -- NULL
```

It evaluates the arguments from left to right and stops at the first non-`NULL` one. The results on this page are from runs in BigQuery on 6 October 2026. Google’s definition is in the [conditional expressions reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#coalesce).

**_Note:_** _Originally published in April 2025. Fully updated in October 2026 with every example re-run._

## 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](/blog/articles/bigquery-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

| Function | Does | Example | Result |
| --- | --- | --- | --- |
| `COALESCE(a, b, ...)` | first non-`NULL` of any number of values | `COALESCE(NULL, NULL, 1)` | `1` |
| `IFNULL(a, b)` | `b` if `a` is `NULL` | `IFNULL(NULL, 0)` | `0` |
| `NULLIF(a, b)` | `NULL` if `a` equals `b` | `NULLIF(5, 5)` | `NULL` |

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

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## 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](/blog/articles/bigquery-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](/blog/articles/bigquery-partitioned-tables) 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 `NULL`s 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](https://www.owox.com/app-signup) 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.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Does COALESCE affect query performance in BigQuery?

In a SELECT list its cost is negligible. In WHERE or JOIN ON, wrapping a column in COALESCE can prevent partition pruning and the use of clustering on that column, so the query scans more data. Write the NULL case as a separate OR condition instead.

Is there a COALESCE function in Google Sheets?

No. For the first non-empty of two cells use =IF(A2<>"", A2, B2). For the first non-empty cell in a range use =INDEX(FILTER(A2:C2, A2:C2<>""), 1, 1). IFNA and IFERROR replace errors, not empty cells.

Can I mix numbers and strings in COALESCE?

No. BigQuery returns 'No matching signature for function COALESCE' with the argument types INT64, STRING. Cast to one type first: COALESCE(CAST(amount AS STRING), 'unknown'). Different numeric types do combine.

Why does COALESCE return an empty string?

Because an empty string is a value, not NULL. Convert empty and blank strings to NULL first: COALESCE(NULLIF(TRIM(col), ''), 'fallback').

Can COALESCE be used with multiple columns?

Yes. COALESCE(mobile, work\_phone, home\_phone) returns the first of the three that has a value in each row. All columns must have the same or a compatible data type.

What is the difference between COALESCE and IFNULL in BigQuery?

IFNULL(a, b) takes exactly two arguments and returns b when a is NULL. COALESCE takes any number and returns the first that is not NULL. With two arguments they give the same result.

Does COALESCE(x, 0) change an average?

Yes. AVG skips NULL values. For the rows 10, NULL, 20, AVG(x) is 15 and AVG(COALESCE(x, 0)) is 10, because the missing row now counts as a zero. The sum stays 30 either way.

What does COALESCE do in BigQuery?

It returns the first argument that is not NULL, reading left to right. COALESCE(NULL, NULL, 'c') returns 'c'. If every argument is NULL, it returns NULL.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.com/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · GA4 user properties in BigQuery: user\_id, user\_pseudo\_id, first touch and LTV · October 16, 2026](/blog/articles/extract-ga4-user-properties-and-metrics)

[Google BigQuery · BigQuery Partitioned Tables: How to Create and Query Them · October 6, 2026](/blog/articles/bigquery-partitioned-tables)

[Google BigQuery · BigQuery statistical aggregate functions: STDDEV, VARIANCE, CORR and median · October 16, 2026](/blog/articles/bigquery-statistical-aggregate-functions)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [date functions](https://www.owox.com/blog/articles/bigquery-date-functions) — /blog/articles/bigquery-date-functions.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [CAST and SAFE_CAST](https://www.owox.com/blog/articles/bigquery-cast-and-safe-cast) — /blog/articles/bigquery-cast-and-safe-cast.md
- [partitions](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google BigQuery · GA4 user properties in BigQuery: user_id, user_pseudo_id, first touch and LTV ·…](https://www.owox.com/blog/articles/extract-ga4-user-properties-and-metrics) — /blog/articles/extract-ga4-user-properties-and-metrics.md
- [Google BigQuery · BigQuery statistical aggregate functions: STDDEV, VARIANCE, CORR and median ·…](https://www.owox.com/blog/articles/bigquery-statistical-aggregate-functions) — /blog/articles/bigquery-statistical-aggregate-functions.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md