BigQuery COALESCE: How to Handle NULLs and Set Defaults

BigQuery COALESCE returns the first argument that is not NULL, reading a list left to right, so it is how you set a default for missing data and how you merge several fallback columns into one. The shape is COALESCE(value1, value2, ..., fallback). A common use is turning a NULL into a display value: COALESCE(region, 'Unknown'). Below is the syntax, how COALESCE differs from IFNULL and NULLIF, the supertype rule that trips people up, and why you rarely need it inside SUM.

COALESCE syntax and how it reads

COALESCE takes two or more arguments and returns the first one that is not NULL. If every argument is NULL, the result is NULL. It stops as soon as it finds a non-null value, so the order of the arguments is the priority order.

SELECT
  user_id,
  COALESCE(nickname, full_name, email, 'anonymous') AS display_name
FROM app.users

For each row this returns the nickname if there is one, otherwise the full name, otherwise the email, and if all three are missing it falls back to the literal 'anonymous'. Putting a non-null literal last guarantees you never get a NULL out, which is what you usually want for a label that goes on a report.

COALESCE vs IFNULL vs NULLIF

These three get mixed up because they all deal with NULL, but they do different jobs.

IFNULL is just COALESCE limited to two arguments, so IFNULL(x, y) and COALESCE(x, y) are identical. Reach for COALESCE when you have more than one fallback. NULLIF runs the other way: it creates a NULL, which is handy for avoiding a divide-by-zero, as in SUM(revenue) / NULLIF(SUM(orders), 0) so an empty group returns NULL instead of erroring.

The supertype rule

Every argument you pass to COALESCE has to share a common type, or a type BigQuery can widen them all into. Mixing a string and a number throws an error like No matching signature for function COALESCE.

-- error: STRING and INT64 have no common supertype
SELECT COALESCE(status_text, 0);

-- fine: cast so both sides are strings
SELECT COALESCE(status_text, CAST(status_code AS STRING));

INT64 and FLOAT64 coalesce cleanly because they widen to a numeric supertype, but a string and a number do not. When the message mentions a signature, a cast on one of the arguments is almost always the fix.

You usually do not need COALESCE inside SUM

A frequent habit from other databases is writing SUM(COALESCE(amount, 0)). In BigQuery this is redundant, because SUM, AVG, COUNT and the other aggregates skip NULL values already. SUM(amount) and SUM(COALESCE(amount, 0)) return the same total.

Where it does matter is arithmetic between columns, because a NULL poisons the whole expression: price - discount is NULL for every row where discount is NULL. Wrap the nullable side: price - COALESCE(discount, 0). So the rule is to coalesce inside row-level math, not around an aggregate.

Merging columns from a join

COALESCE is the clean way to combine a preferred source with a fallback after a LEFT JOIN, for example a manually overridden value on top of a default one.

SELECT
  p.product_id,
  COALESCE(o.override_name, p.default_name) AS product_name
FROM catalog.products AS p
LEFT JOIN catalog.overrides AS o
  ON o.product_id = p.product_id

When an override row exists, its name wins; when the left join finds no match and o.override_name is NULL, the default name is used. This is the pattern behind most "use the custom value if set, otherwise the standard one" logic, and it reads clearly enough that the next person to open the query understands it at a glance.

Ask it in plain English instead

Deciding where a default belongs, inside the math or around it, and which fallback should win, is exactly the kind of detail that makes hand-written SQL slow. Agentsql connects read-only to BigQuery, turns a plain-English question into the correct Standard SQL with the null handling worked out, runs it, and shows you the query so you can confirm the defaults are where you expect. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.