BigQuery IF, CASE, IFNULL, NULLIF and COALESCE, with tested examples

The table used in every example

WITH orders AS (
  SELECT 1 AS order_id, 'paid' AS status,
         120.0 AS amount, 3 AS items, 'web' AS channel
  UNION ALL SELECT 2, 'paid', 80.0, 0, 'email'
  UNION ALL SELECT 3, 'refunded', 200.0, 2, NULL
  UNION ALL SELECT 4, NULL, 50.0, 1, 'ads'
)

Order 3 has no channel and order 4 has no status. Real tables have rows like these, and they are where conditional logic goes wrong.

IF

SELECT
  order_id,
  IF(status = 'paid', amount, 0) AS paid_amount
FROM orders;

IF evaluates the condition and returns the second argument when it is true, otherwise the third. A condition that is NULL counts as not true, so order 4, with no status, falls to the third argument.

Inside an aggregate, IF builds a conditional total: SUM(IF(status = 'paid', amount, 0)) returned 200. COUNTIF(condition) is the shorter form for counting.

The IF ... THEN ... ELSE ... END IF statement also exists, but only in BigQuery scripting, between statements, not inside a SELECT.

The NULL trap in every condition

This is the mistake behind most wrong counts:

SELECT COUNTIF(status != 'refunded') AS not_refunded
FROM orders;

It returned 2, not 3. Order 4 has no status, and NULL != 'refunded' is not true but NULL, so the row is not counted. The same happens in WHERE, IF and CASE.

If a missing status should count as “not refunded”, say so:

IS DISTINCT FROM treats two NULLs as equal and a NULL and a value as different, which is what most people expect != to do.

CASE

The searched form checks conditions in order and stops at the first that is true:

SELECT
  order_id,
  CASE
    WHEN amount >= 100 THEN 'large'
    WHEN amount >= 60 THEN 'medium'
    ELSE 'small'
  END AS size
FROM orders;

Order matters: put >= 60 first and every large order becomes “medium”. Without ELSE, a row that matches nothing gets NULL.

The simple form cannot find NULL

CASE status WHEN 'paid' THEN ... END compares status with each value using =. Since nothing equals NULL, WHEN NULL never matches:

To handle NULL, use the searched form with IS NULL as the first branch.

Every branch must return the same type

SELECT CASE WHEN TRUE THEN 1 ELSE 'none' END;

fails with “No matching signature for operator CASE; all THEN/ELSE arguments must be coercible to a common type but found: INT64, STRING”. Return the same type from every branch, or CAST one of them. Numbers do mix: an INT64 branch and a FLOAT64 branch both come back as FLOAT64. The same rule applies to IF and COALESCE.

IFNULL and COALESCE

IFNULL(x, y) is COALESCE(x, y) with two arguments. COALESCE takes as many as you like and returns the first that is not NULL. Filling a NULL changes what an average or a count means, so decide it once and write it down; the COALESCE guide shows the effect on an average.

NULLIF and division by zero

SELECT 10 / 0 fails the whole query with “division by zero”. In a table with one empty row out of millions, that is one failed scheduled report.

SELECT
  order_id,
  amount / NULLIF(items, 0) AS per_item
FROM orders;

NULLIF(items, 0) returns NULL when items is 0, and dividing by NULL gives NULL instead of an error. SAFE_DIVIDE(amount, items) does the same in one call. Either way, an AVG over the result skips that row. That is right when you want the typical order, but not for a total: an average of per-row ratios ignores the rows that became NULL and weights small rows like large ones, so for a figure such as cost per order divide the sums instead. The mathematical functions guide has the other SAFE_ functions.

Which one to use

• One condition: IF.
• Several, in order: searched CASE, with IS NULL first if NULLs are possible.
• A default for a missing value: IFNULL, or COALESCE for a chain of fallbacks.
• A ratio: SAFE_DIVIDE, or NULLIF on the divisor.
• A comparison where either side can be NULL: IS DISTINCT FROM.

All the function families are mapped in the BigQuery functions overview, and the clauses these expressions sit in are in WHERE, HAVING and QUALIFY.

Write the rule once

A CASE that groups channels, or an IF that decides what counts as a sale, is a business rule. Copied into five queries, it becomes five slightly different rules, and the reports stop agreeing. In OWOX Data Marts that query is published once as a Data Mart with a description on each column, so the rule lives in one place and every report, spreadsheet or dashboard reads the same result. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryHow to Load Data into BigQuery: Every Method and When to Use ItOctober 6, 2026→Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryBigQuery Aggregate Functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and MoreOctober 16, 2026→

See all articles →