# 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. Source: https://docs.querylab.io/bigquery/conditional-expressions/ Updated: 2026-11-04 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 amount** ```sql 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 (4 rows): | amount | size | | --- | --- | | NULL | small | | 5 | small | | 60 | medium | | 250 | large | Without `ELSE`, the result is NULL: **no match and no ELSE** ```sql SELECT CASE WHEN 1 > 2 THEN 'yes' END AS answer ``` Result in BigQuery (1 row): | answer | | --- | | 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`: **two forms on a NULL status** ```sql 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 (1 row): | simple_form | searched_form | | --- | --- | | other | missing | BigQuery accepts this `WHEN NULL`, even though it [rejects `x = NULL`](https://docs.querylab.io/bigquery/null-handling/) written directly. ## Every branch needs one type An integer and a string have no common type: **INT64 and STRING branches** ```sql SELECT CASE WHEN 1 < 2 THEN 1 ELSE 'none' END AS answer ``` BigQuery rejects this query: ```text 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 branches** ```sql SELECT CASE WHEN 1 < 2 THEN 1 ELSE 2.5 END AS answer ``` Result in BigQuery (1 row): | answer | | --- | | 1.0 | [CAST and type conversion](https://docs.querylab.io/bigquery/casting-and-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 amount** ```sql 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 (1 row): | with_if | with_case | | --- | --- | | 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: **IFNULL, COALESCE and NULLIF** ```sql 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 (1 row): | ifnull_result | coalesce_result | nullif_result | per_item | | --- | --- | --- | --- | | 0 | 5 | NULL | NULL | Names from other databases fail with `Function not found`. Use `IFNULL` for `NVL`, `IF` for `IIF` and `CASE` for `DECODE`: **NVL (Oracle)** ```sql SELECT NVL(NULL, 1) AS answer ``` BigQuery rejects this query: ```text Function not found: NVL at [1:8] ``` **IIF (SQL Server)** ```sql SELECT IIF(1 < 2, 'yes', 'no') AS answer ``` BigQuery rejects this query: ```text Function not found: IIF; Did you mean if? at [1:8] ``` **DECODE (Oracle)** ```sql SELECT DECODE(1, 1, 'one', 'other') AS answer ``` BigQuery rejects this query: ```text Function not found: DECODE at [1:8] ``` ## Only the branch taken is evaluated Dividing by zero is an error in BigQuery: **division by zero** ```sql WITH orders AS (SELECT * FROM UNNEST([0, 4]) AS items) SELECT items, 20 / items AS per_item FROM orders ``` BigQuery rejects this query: ```text division by zero: 20 / 0 ``` A `CASE` that guards the division runs it only for rows that reach that branch: **a guarded division** ```sql 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 (2 rows): | items | per_item | | --- | --- | | 0 | 0.0 | | 4 | 5.0 | `IF`, `COALESCE` and `IFNULL` also evaluate only what they need. `SAFE_DIVIDE` and the [`SAFE.` prefix](https://docs.querylab.io/bigquery/safe-functions/) 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_OR** ```sql 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 (1 row): | paid_orders | paid_amount | not_paid | any_refund | | --- | --- | --- | --- | | 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: **alias in WHERE** ```sql WITH orders AS (SELECT 30 AS amount) SELECT amount * 2 AS doubled FROM orders WHERE doubled > 10 ``` BigQuery rejects this query: ```text 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 BY** ```sql 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 (2 rows): | size | orders | | --- | --- | | big | 2 | | small | 1 | To reuse an alias in the same `SELECT` list, see [reusable column aliases](https://docs.querylab.io/bigquery/column-aliases/). If an alias matches a column name, [`GROUP BY` picks the alias](https://docs.querylab.io/bigquery/group-by/). ## 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 MOD** ```sql SELECT 7 / 2 AS slash, DIV(7, 2) AS div_function, MOD(7, 2) AS remainder ``` Result in BigQuery (1 row): | slash | div_function | remainder | | --- | --- | --- | | 3.5 | 3 | 1 | Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Conditional expressions](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions) - [Aggregate functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions)