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:
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
| Row | amount | size |
|---|---|---|
| 1 | null | small |
| 2 | 5 | small |
| 3 | 60 | medium |
| 4 | 250 | large |
Without ELSE, the result is NULL:
| Row | answer |
|---|---|
| 1 | null |
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:
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
| Row | simple_form | searched_form |
|---|---|---|
| 1 | other | missing |
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:
SELECT CASE WHEN 1 < 2 THEN 1 ELSE 'none' END AS answerNo 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:
| Row | answer |
|---|---|
| 1 | 1.0 |
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:
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
| Row | with_if | with_case |
|---|---|---|
| 1 | small | null |
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:
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
| Row | ifnull_result | coalesce_result | nullif_result | per_item |
|---|---|---|---|---|
| 1 | 0 | 5 | null | null |
Names from other databases fail with Function not found. Use IFNULL for NVL, IF for IIF and CASE for DECODE:
SELECT NVL(NULL, 1) AS answerFunction not found: NVL at [1:8]
SELECT IIF(1 < 2, 'yes', 'no') AS answerFunction not found: IIF; Did you mean if? at [1:8]
SELECT DECODE(1, 1, 'one', 'other') AS answerFunction not found: DECODE at [1:8]
Only the branch taken is evaluated
Dividing by zero is an error in BigQuery:
WITH orders AS (SELECT * FROM UNNEST([0, 4]) AS items) SELECT items, 20 / items AS per_item FROM orders
division by zero: 20 / 0
A CASE that guards the division runs it only for rows that reach that branch:
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
| Row | items | per_item |
|---|---|---|
| 1 | 0 | 0.0 |
| 2 | 4 | 5.0 |
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”:
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
| Row | paid_orders | paid_amount | not_paid | any_refund |
|---|---|---|---|---|
| 1 | 2 | 50 | 1 | true |
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:
WITH orders AS (SELECT 30 AS amount) SELECT amount * 2 AS doubled FROM orders WHERE doubled > 10
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:
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
| Row | size | orders |
|---|---|---|
| 1 | big | 2 |
| 2 | small | 1 |
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:
SELECT 7 / 2 AS slash, DIV(7, 2) AS div_function, MOD(7, 2) AS remainder| Row | slash | div_function | remainder |
|---|---|---|---|
| 1 | 3.5 | 3 | 1 |