Docs/Querying/CASE, IF and COALESCE
Querying

CASE, IF and COALESCE in BigQuery

CASE branches must share a type, CASE x WHEN NULL never matches, and IF treats a NULL condition as false. BigQuery has IFNULL, COALESCE and NULLIF but no NVL, IIF or DECODE, and expressions evaluate only in the branch taken.

Practice this in free lessons on learn.querylab.io: Expressions, aliases and CASE and 1 more.

BigQuery’s conditional expressions are CASE, IF, COALESCE, IFNULL and NULLIF. CASE returns the result of the first WHEN that is true, or NULL if nothing matches and there is no ELSE. BigQuery is strict about types: every branch must convert to one common type, or the query fails. NVL, IIF and DECODE from other databases don’t exist.

CASE stops at the first match

Order the conditions from most to least specific. A NULL amount fails every comparison and lands in ELSE, which is easy to miss:

sizes from an amountOpen in Querylab.io
12345678910
WITH orders AS (SELECT * FROM UNNEST([5, 60, 250, NULL]) AS amount)
SELECT
  amount,
  CASE
    WHEN amount >= 100 THEN 'large'
    WHEN amount >= 50 THEN 'medium'
    ELSE 'small'
  END AS size
FROM orders
ORDER BY amount
Result in BigQuery
Rowamountsize
1nullsmall
25small
360medium
4250large
4 rows

Without ELSE, the result is NULL:

no match and no ELSEOpen in Querylab.io
1
SELECT CASE WHEN 1 > 2 THEN 'yes' END AS answer
Result in BigQuery
Rowanswer
1null
1 row

CASE x WHEN NULL never matches

The short form compares with =, and = with a NULL is never true. Use the long form with IS NULL:

two forms on a NULL statusOpen in Querylab.io
12345
WITH orders AS (SELECT CAST(NULL AS STRING) AS status)
SELECT
  CASE status WHEN NULL THEN 'missing' ELSE 'other' END AS simple_form,
  CASE WHEN status IS NULL THEN 'missing' ELSE 'other' END AS searched_form
FROM orders
Result in BigQuery
Rowsimple_formsearched_form
1othermissing
1 row

BigQuery accepts this WHEN NULL, even though it rejects x = NULL written directly.

Every branch needs one type

An integer and a string have no common type:

INT64 and STRING branchesBigQuery rejects this query
1
SELECT CASE WHEN 1 < 2 THEN 1 ELSE 'none' END AS answer
Error in BigQuery

No matching signature for operator CASE; all THEN/ELSE arguments must be coercible to a common type but found: INT64, STRING; actual argument types (WHEN THEN) ELSE: (BOOL INT64) STRING at [1:8]

Use CAST on one branch. Numbers convert on their own, and the whole column takes the wider type, so the integer branch below comes back as 1.0:

INT64 and FLOAT64 branchesOpen in Querylab.io
1
SELECT CASE WHEN 1 < 2 THEN 1 ELSE 2.5 END AS answer
Result in BigQuery
Rowanswer
11.0
1 row

CAST and type conversion lists what converts.

IF treats NULL as false

IF(condition, a, b) returns b when the condition is NULL, so a missing amount below becomes small. A CASE with both tests written out returns NULL and keeps the row visibly unknown:

IF and CASE on a NULL amountOpen in Querylab.io
12345
WITH orders AS (SELECT CAST(NULL AS INT64) AS amount)
SELECT
  IF(amount > 10, 'big', 'small') AS with_if,
  CASE WHEN amount > 10 THEN 'big' WHEN amount <= 10 THEN 'small' END AS with_case
FROM orders
Result in BigQuery
Rowwith_ifwith_case
1smallnull
1 row

Functions for NULL

IFNULL(a, b) is a two-argument COALESCE. COALESCE returns its first non-NULL argument. NULLIF(a, b) returns NULL when the two are equal, which turns a sentinel into a real NULL and protects a division:

IFNULL, COALESCE and NULLIFOpen in Querylab.io
123456789
WITH orders AS (
  SELECT CAST(NULL AS INT64) AS discount, '' AS note, 0 AS items, 30 AS amount
)
SELECT
  IFNULL(discount, 0) AS ifnull_result,
  COALESCE(discount, NULL, 5) AS coalesce_result,
  NULLIF(note, '') AS nullif_result,
  amount / NULLIF(items, 0) AS per_item
FROM orders
Result in BigQuery
Rowifnull_resultcoalesce_resultnullif_resultper_item
105nullnull
1 row

Names from other databases fail with Function not found. Use IFNULL for NVL, IF for IIF and CASE for DECODE:

NVL (Oracle)BigQuery rejects this query
1
SELECT NVL(NULL, 1) AS answer
Error in BigQuery

Function not found: NVL at [1:8]

IIF (SQL Server)BigQuery rejects this query
1
SELECT IIF(1 < 2, 'yes', 'no') AS answer
Error in BigQuery

Function not found: IIF; Did you mean if? at [1:8]

DECODE (Oracle)BigQuery rejects this query
1
SELECT DECODE(1, 1, 'one', 'other') AS answer
Error in BigQuery

Function not found: DECODE at [1:8]

Only the branch taken is evaluated

Dividing by zero is an error in BigQuery:

division by zeroBigQuery rejects this query
123
WITH orders AS (SELECT * FROM UNNEST([0, 4]) AS items)
SELECT items, 20 / items AS per_item
FROM orders
Error in BigQuery

division by zero: 20 / 0

A CASE that guards the division runs it only for rows that reach that branch:

a guarded divisionOpen in Querylab.io
1234
WITH orders AS (SELECT * FROM UNNEST([0, 4]) AS items)
SELECT items, CASE WHEN items = 0 THEN 0 ELSE 20 / items END AS per_item
FROM orders
ORDER BY items
Result in BigQuery
Rowitemsper_item
100.0
245.0
2 rows

IF, COALESCE and IFNULL also evaluate only what they need. SAFE_DIVIDE and the SAFE. prefix are shorter when NULL is the answer you want.

Conditional aggregates

COUNTIF(condition) replaces SUM(CASE WHEN ... THEN 1 ELSE 0 END). LOGICAL_OR answers “did any row match”:

COUNTIF, SUM(IF) and LOGICAL_OROpen in Querylab.io
1234567891011
WITH orders AS (
  SELECT * FROM UNNEST([
    STRUCT('paid' AS status, 30 AS amount), ('paid', 20), ('refunded', 15), (NULL, 5)
  ])
)
SELECT
  COUNTIF(status = 'paid') AS paid_orders,
  SUM(IF(status = 'paid', amount, 0)) AS paid_amount,
  COUNTIF(status != 'paid') AS not_paid,
  LOGICAL_OR(status = 'refunded') AS any_refund
FROM orders
Result in BigQuery
Rowpaid_orderspaid_amountnot_paidany_refund
12501true
1 row

The order with a NULL status appears in neither count: status != 'paid' is NULL for it, and COUNTIF counts only true. That is why paid_orders plus not_paid is 3 for 4 rows.

Where an alias can be used

An alias from SELECT isn’t visible in WHERE, because WHERE runs first:

alias in WHEREBigQuery rejects this query
12
WITH orders AS (SELECT 30 AS amount)
SELECT amount * 2 AS doubled FROM orders WHERE doubled > 10
Error in BigQuery

Unrecognized name: doubled at [2:48]

It is visible in GROUP BY, HAVING, QUALIFY and ORDER BY, so you can group by a CASE once without repeating it:

alias in GROUP BYOpen in Querylab.io
12345
WITH orders AS (SELECT * FROM UNNEST([5, 60, 250]) AS amount)
SELECT IF(amount >= 50, 'big', 'small') AS size, COUNT(*) AS orders
FROM orders
GROUP BY size
ORDER BY size
Result in BigQuery
Rowsizeorders
1big2
2small1
2 rows

To reuse an alias in the same SELECT list, see reusable column aliases. If an alias matches a column name, GROUP BY picks the alias.

Division returns a float

/ always returns FLOAT64 in BigQuery, even for two integers, where Postgres and SQL Server truncate. Use DIV for integer division:

/, DIV and MODOpen in Querylab.io
1
SELECT 7 / 2 AS slash, DIV(7, 2) AS div_function, MOD(7, 2) AS remainder
Result in BigQuery
Rowslashdiv_functionremainder
13.531
1 row
Open the playground. It is the real app on public datasets and needs no sign-in.