BigQuery IFNULL: How to Handle IF NULL and NULLIF in BigQuery SQL

BigQuery IFNULL replaces a null with a value you choose: IFNULL(expr, null_result) returns the fallback when the first argument is NULL, and returns the first argument otherwise. NULLIF does the exact opposite, turning a value you name into a NULL. Between them they cover most day-to-day null handling in GoogleSQL, and the two mistakes people make most often are reaching for ISNULL, which BigQuery does not have, and writing = NULL instead of IS NULL. Below is the syntax for both functions, how they differ from COALESCE, the type error that stops IFNULL working, and the division-by-zero pattern NULLIF exists for.

The four null functions at a glance

Pick the row that matches what you are trying to do and the rest of this page is detail.

IFNULL and COALESCE both remove nulls. NULLIF creates them. IS NULL only asks the question. Confusing the first three is behind most of the null bugs that reach a dashboard.

What is IFNULL in BigQuery?

IFNULL is a conditional expression with the signature IFNULL(expr, null_result). Google's GoogleSQL reference states that if expr evaluates to NULL it returns null_result, otherwise it returns expr, and adds that "if expr doesn't evaluate to NULL, null_result isn't evaluated." Both arguments can be any type as long as they are implicitly coercible to a common supertype, and the return type is that supertype.

SELECT
  customer_id,
  IFNULL(discount_code, 'none')  AS discount_code,
  IFNULL(refund_amount, 0)       AS refund_amount
FROM shop.orders

That "isn't evaluated" clause is worth more than it looks. Because the fallback is skipped entirely on rows where the column already has a value, you can put something genuinely expensive in it, such as a scalar subquery that looks up a default from another table, and pay for it only on the rows that are actually missing. It also means a fallback that would error, for example a cast that fails, is harmless as long as it never has to run.

How do you replace null with 0 in BigQuery?

Wrap the column and give 0 as the second argument: IFNULL(refund_amount, 0). That is the whole answer for the common case of a numeric column where missing should read as zero. COALESCE(refund_amount, 0) is exactly equivalent, and CASE WHEN refund_amount IS NULL THEN 0 ELSE refund_amount END is the long way round.

SELECT
  channel,
  SUM(spend)              AS spend_ignoring_nulls,
  SUM(IFNULL(spend, 0))   AS spend_with_zeros,
  IFNULL(SUM(spend), 0)   AS spend_or_zero_if_no_rows
FROM ads.daily_spend
GROUP BY channel

The first two columns always agree, because SUM already ignores nulls, so wrapping the input in IFNULL changes nothing and just costs you a function call. The third is different and is the one people actually want: it protects against the group having no non-null rows at all, where SUM itself returns NULL. Put another way, IFNULL inside an aggregate is usually pointless and IFNULL around an aggregate is usually the fix.

Where the zero substitution genuinely matters is in a LEFT JOIN. If you join a channel list to a spend table and a channel had no spend that week, every column from the right side arrives as NULL, and a report that quietly drops those rows looks like the channel does not exist. This is a routine problem for anyone stitching several ad platforms and a storefront into one marketing dashboard that unifies every channel, where a missing row and a genuine zero mean very different things and only one of them should be reported as $0.

What is the difference between IFNULL and COALESCE in BigQuery?

The number of arguments, and nothing else. IFNULL takes exactly two. COALESCE takes as many as you like and returns the first that is not null. Google's documentation is explicit that IFNULL is a "Synonym for COALESCE(expr, null_result)", so in the two-argument case they are the same function wearing different names. There is no performance argument for choosing one over the other.

Practically, use IFNULL when there is a single obvious default and COALESCE when you are picking the best available value from several columns, for instance COALESCE(shipping_address, billing_address, 'unknown'). If you are writing SQL that has to run on another warehouse later, COALESCE is the portable choice. Our guide to BigQuery COALESCE covers the multi-argument patterns and the supertype rules in more depth.

Is there an ISNULL function in BigQuery?

No. ISNULL does not exist in GoogleSQL and BigQuery returns an unrecognized function error if you use it. It is SQL Server and MySQL syntax, and it is the single most common thing people search for when they arrive from another warehouse. The direct replacement is IFNULL, which takes the same two arguments in the same order.

The last row is the one that causes real bugs rather than a clean error. MySQL's single-argument ISNULL(col) returns a boolean, so it is a test, while SQL Server's two-argument ISNULL(col, x) is a replacement. They share a name and do different jobs. If you are translating a query and are not certain which one the original meant, look at whether the result is being compared or selected before you pick IFNULL or IS NULL.

What does NULLIF do in BigQuery?

NULLIF runs the substitution backwards. The documented behavior of NULLIF(expr, expr_to_match) is that it returns NULL if expr = expr_to_match evaluates to TRUE, and otherwise returns expr. So you use it to say "treat this particular value as missing data."

SELECT
  NULLIF(country, '')        AS country,      -- empty string becomes a real NULL
  NULLIF(status, 'unknown')  AS status,       -- a placeholder becomes a real NULL
  NULLIF(price, 0)           AS price         -- 0 meaning "not set" becomes NULL
FROM crm.accounts

This matters more than it sounds in imported data, where missing values arrive as empty strings, the literal text "NULL", -1, or 0 depending on who built the export. Those placeholders are counted by COUNT(col) and included in AVG(col), which quietly drags an average toward zero. Converting them to genuine nulls with NULLIF puts them back outside the aggregate, where they belong. The pattern chains cleanly with IFNULL too: IFNULL(NULLIF(TRIM(notes), ''), 'no notes') trims whitespace, treats a blank as missing, and then labels it.

How do you avoid division by zero in BigQuery?

Wrap the denominator in NULLIF: clicks / NULLIF(impressions, 0). Google's operator reference states plainly that "Divide by zero operations return an error", so unlike some databases BigQuery will fail the whole query rather than return infinity. Turning the zero into a NULL makes the division return NULL for that row, and the query completes.

SELECT
  campaign,
  clicks / NULLIF(impressions, 0)                 AS ctr,
  SAFE_DIVIDE(clicks, impressions)                AS ctr_safe,
  IFNULL(SAFE_DIVIDE(clicks, impressions), 0)     AS ctr_zero_when_undefined
FROM ads.campaign_stats

The docs point at SAFE_DIVIDE and IEEE_DIVIDE as the purpose-built alternatives, and SAFE_DIVIDE is the one to reach for in most analytics queries: it returns NULL instead of erroring and it is one function rather than a nested pair. Keep the NULLIF form when the denominator is a longer expression you do not want to repeat, or when you are writing SQL that also has to run somewhere without SAFE_DIVIDE.

The third column shows the decision that actually needs a human. A campaign with no impressions has an undefined click-through rate, not a rate of zero. Showing 0% averages it in with genuinely bad campaigns and makes the account look worse than it is. Leave it NULL unless a stakeholder has explicitly asked for zeros.

How do you check if a value is null in BigQuery?

Use the IS NULL operator: WHERE region IS NULL, or IS NOT NULL for the inverse. Do not write WHERE region = NULL. Comparing any value to NULL produces unknown rather than TRUE, and a WHERE clause keeps only rows that evaluate to TRUE, so that filter returns zero rows every time without raising an error. It is a silent failure, which is what makes it dangerous.

-- returns nothing, always, and never errors
SELECT * FROM crm.accounts WHERE region = NULL;

-- correct
SELECT * FROM crm.accounts WHERE region IS NULL;

-- count nulls and non-nulls in one pass
SELECT
  COUNT(*)                                   AS rows_total,
  COUNT(region)                              AS rows_with_region,
  COUNTIF(region IS NULL)                    AS rows_missing_region
FROM crm.accounts

That last query is the quickest null audit in BigQuery. COUNT(*) counts rows, COUNT(col) counts only non-null values of that column, and the gap between them is your null count, which COUNTIF(col IS NULL) states directly. The same asymmetry explains a result people find surprising after a LEFT JOIN UNNEST, covered in our note on UNNEST and empty arrays.

Why does IFNULL give a type error in BigQuery?

Because both arguments must be implicitly coercible to a common supertype, and BigQuery will not quietly convert a number into text to make your query run. IFNULL(order_count, 'none') fails with a "No matching signature" style error, because INT64 and STRING have no common supertype.

-- fails: INT64 and STRING have no common supertype
SELECT IFNULL(order_count, 'none') FROM crm.accounts;

-- fix 1: keep it numeric
SELECT IFNULL(order_count, 0) FROM crm.accounts;

-- fix 2: cast first, when you genuinely want text out
SELECT IFNULL(CAST(order_count AS STRING), 'none') FROM crm.accounts;

INT64 and FLOAT64 do share a supertype, so IFNULL(int_col, 0.0) is accepted, but it silently widens the whole column to FLOAT64 and your integers start rendering with decimal points downstream. Match the type deliberately rather than letting coercion pick for you. The same rule governs CASE and IF, where every branch has to agree on a type, which our guide to the BigQuery IF statement works through alongside the NULL condition that falls to the else branch.

When a null is a data problem, not a SQL problem

Every function on this page is a display decision made at query time. None of them fix the underlying gap, and reaching for IFNULL reflexively is how a broken upstream feed stays invisible for a quarter: the dashboard reads 0 instead of blank, nobody investigates, and the number is wrong in a way that looks fine.

Before you paper over a column, it is worth knowing why it is empty. A null that means "this customer has genuinely never ordered" deserves a 0. A null that means "the sync failed on Tuesday" deserves a fix, and showing it as 0 destroys the evidence. The COUNT(*) against COUNT(col) audit above, run per day rather than over the whole table, usually tells you which one you are looking at within a minute, because a real data gap has a start date and a genuine absence does not.

Skip the null handling and just ask the question

Remembering that ISNULL does not exist, that = NULL matches nothing, that SUM already skips nulls, and that a zero denominator errors rather than returning infinity is a lot of surface area for a question as ordinary as "what was our click-through rate by campaign last month". Agentsql connects read-only to BigQuery, turns the plain-English question into correct GoogleSQL with the null and division handling already right, runs it, and shows you the SQL so you can check what it did with the missing rows before anyone acts on the number. See the BigQuery integration for how the read-only connection works, or the SQL query generator for how the translation happens.