BigQuery IF Statement: IF, IF ELSE, IFNULL, and CASE WHEN Explained

BigQuery has two separate things called IF, and mixing them up is the most common reason a conditional query fails. Inside a SELECT you use the IF() function: IF(condition, true_result, else_result), which returns one value per row. In a script or stored procedure you use the IF ... THEN ... ELSEIF ... ELSE ... END IF statement, which controls which SQL actually runs. The function cannot control flow, and the statement cannot be used inside a SELECT list. Below is the syntax for both, how IF compares to CASE WHEN, the IFNULL and NULLIF shortcuts, and the NULL behavior that silently sends rows down the wrong branch.

BigQuery IF statement syntax in a SELECT

The IF() function takes exactly three arguments. The first must be a boolean expression. If it evaluates to TRUE, you get the second argument; otherwise you get the third.

SELECT
  order_id,
  order_total,
  IF(order_total >= 100, 'large', 'standard') AS order_size
FROM shop.orders

Two details from Google's own reference matter here. The else_result is not evaluated when the condition is TRUE, and the true_result is not evaluated when the condition is FALSE. That short-circuiting means you can safely put an expensive or error-prone expression in a branch that will not run. The other detail is the return type: it is the supertype of the two result arguments, so both branches must be coercible to a common type. Returning a STRING from one branch and an INT64 from the other is an error, not a silent cast.

What happens when the condition is NULL

This is the part that quietly produces wrong numbers, and it is worth committing to memory. Google's documentation states that true_result is not evaluated if the condition evaluates to FALSE or NULL. A NULL condition is not an error and it does not return NULL. It takes the else branch.

SELECT
  IF(NULL, 'yes', 'no') AS result
-- returns 'no'

So if you write IF(discount_pct > 0, 'discounted', 'full price') and discount_pct is NULL for rows where no discount was ever recorded, those rows are labeled 'full price'. That may well be what you want. The problem is that it looks identical in the output to rows you genuinely checked and found undiscounted, so a NULL data-quality issue disappears into a legitimate-looking bucket. When the difference matters, test for it explicitly:

SELECT
  CASE
    WHEN discount_pct IS NULL THEN 'unknown'
    WHEN discount_pct > 0 THEN 'discounted'
    ELSE 'full price'
  END AS discount_status
FROM shop.orders

How do you write IF ELSE in BigQuery?

There is no ELSE keyword in the IF function, because the third argument is the else. For a genuine two-way branch, IF(condition, a, b) is the whole thing. For three or more branches, people reach for nested IF calls, and that is where readability collapses:

-- works, but do not do this
SELECT IF(score >= 90, 'A', IF(score >= 80, 'B', IF(score >= 70, 'C', 'F'))) AS grade
FROM school.results

Once you need more than two outcomes, switch to CASE WHEN. It reads top to bottom, each condition sits on its own line, and adding a band later does not mean rebalancing parentheses.

SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM school.results

CASE evaluates conditions in order and returns the first one that is TRUE, so the remaining branches are skipped. That ordering is load-bearing: put score >= 70 first and every passing student gets a C. If no condition matches and there is no ELSE, CASE returns NULL. There is more on ordering and the conditional-aggregation patterns in our guide to BigQuery CASE WHEN.

IF vs CASE WHEN: which should you use?

They compile to the same kind of conditional logic, so this is a readability decision rather than a performance one.

IFNULL, NULLIF, and how to return zero if null in BigQuery

Three shorthands cover most of the conditional logic people write IF for. IFNULL(expr, null_result) returns the replacement when the expression is NULL and the expression otherwise. Google's reference describes it plainly as a synonym for COALESCE(expr, null_result), so the choice between them is style, except that COALESCE takes any number of fallbacks.

SELECT
  IFNULL(refund_amount, 0)              AS refund_amount,
  COALESCE(nickname, full_name, 'n/a')  AS display_name
FROM app.users

That first line is the answer to "how do I return zero if null in BigQuery". Wrapping a possibly-null number in IFNULL(x, 0) is the standard fix for reports where a blank cell should read as zero. Note that you rarely need it inside an aggregate: SUM() already ignores NULLs, so SUM(IFNULL(amount, 0)) and SUM(amount) return the same total. The one case where it does change the answer is AVG(), because turning NULLs into zeros adds them to the denominator. More patterns are in our guide to BigQuery COALESCE.

NULLIF(expr, expr_to_match) is the mirror image: it returns NULL when the two arguments are equal, and the first argument otherwise. Its classic use is guarding a division:

SELECT
  clicks,
  impressions,
  clicks / NULLIF(impressions, 0) AS ctr
FROM ads.daily

If impressions is zero, NULLIF turns it into NULL and the division returns NULL instead of raising a division-by-zero error. BigQuery also offers SAFE_DIVIDE(clicks, impressions), which returns NULL on a zero denominator without the extra call. Either is fine; SAFE_DIVIDE is shorter, NULLIF is portable to warehouses that lack it.

Counting and summing with a condition

A lot of IF usage in the wild is really conditional aggregation. BigQuery has a dedicated function for the counting half, COUNTIF(), which takes a boolean expression and returns an INT64 count of the rows where it was TRUE.

SELECT
  country,
  COUNTIF(status = 'refunded')                    AS refunded_orders,
  COUNTIF(order_total >= 100)                     AS large_orders,
  SUM(IF(status = 'refunded', order_total, 0))    AS refunded_value
FROM shop.orders
GROUP BY country

Use COUNTIF whenever you are counting, because COUNTIF(x) is clearer than COUNT(IF(x, 1, NULL)) and much clearer than SUM(IF(x, 1, 0)). Use SUM(IF(condition, value, 0)) when you are adding up an amount rather than counting rows. One caution from the reference: COUNTIF(DISTINCT ...) is generally not useful, and Google recommends COUNT with DISTINCT IF instead.

The same shape works well for monitoring-style summaries, where you bucket rows by a threshold and watch the counts move:

SELECT
  endpoint,
  COUNTIF(response_time_ms > 1000) AS slow_requests,
  COUNTIF(status_code >= 500)      AS server_errors
FROM logs.requests
WHERE request_date = CURRENT_DATE()
GROUP BY endpoint

A query like that tells you what already went wrong. If you want the same thresholds evaluated continuously rather than whenever somebody remembers to run the report, that job belongs to a monitor that checks your sites and APIs every thirty seconds and pages you on failure, with the warehouse query kept for the after-the-fact analysis.

The procedural IF statement in BigQuery scripts

Everything above returns a value per row. The other IF decides which statements execute at all, and it only works in scripts, stored procedures and other procedural contexts. The syntax from the BigQuery procedural language reference is:

IF condition THEN
  [sql_statement_list]
[ELSEIF condition THEN
  sql_statement_list]
[...]
[ELSE
  sql_statement_list]
END IF;

Note the differences from the function: the branches are lists of statements rather than values, the keyword is ELSEIF as one word, and the block closes with END IF;. A working example that checks for a row before acting on it:

DECLARE target_product_id INT64 DEFAULT 103;

IF EXISTS (SELECT 1 FROM schema.products WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSEIF EXISTS (SELECT 1 FROM schema.more_products WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product in more_products');
ELSE
  SELECT CONCAT('did not find product');
END IF;

Two documented limits are worth knowing before you build anything elaborate on this. Blocks and conditional statements such as BEGIN/END, IF/ELSE/END IF and WHILE/END WHILE have a maximum nesting level of 50. And IF cannot be nested inside an EXECUTE IMMEDIATE statement, so dynamic SQL that generates conditional blocks will not run.

Why does my BigQuery IF statement give a syntax error?

Almost always because the two forms got crossed. The error usually falls into one of these:

• Using THEN inside a SELECT. SELECT IF x > 5 THEN 'big' ELSE 'small' END is not valid GoogleSQL. In a query it is IF(x > 5, 'big', 'small'), with parentheses and commas.
• Using the function where a statement belongs. IF() returns a value; it cannot decide whether an INSERT runs. That needs the procedural form, in a script.
• Writing ELSE IF as two words. The procedural keyword is ELSEIF. Two words is a syntax error.
• Forgetting END IF. The procedural block must close with END IF;, and the semicolon is part of it.
• Mismatched branch types. Both results of IF() must coerce to a common supertype, so returning a number in one branch and a string in the other fails.
• Legacy SQL syntax. Older examples on the web use Legacy SQL conventions. Confirm you are on GoogleSQL; our note on standard SQL vs legacy SQL covers how the dialects differ.

Can you use IF in a WHERE clause in BigQuery?

You can, because IF() is an ordinary expression and a WHERE clause accepts any expression that resolves to a boolean. It is almost never the clearest way to write the filter, though. WHERE IF(region = 'US', order_total > 100, order_total > 50) is valid, and the same rule written with plain boolean logic reads better: WHERE (region = 'US' AND order_total > 100) OR (region != 'US' AND order_total > 50).

Where IF genuinely helps in a WHERE clause is when a parameter changes the filter, for instance letting one query serve both a filtered and an unfiltered mode. Keep in mind that filtering on a computed expression can stop BigQuery from pruning partitions, which turns a cheap query into a full scan and costs real money on a large table. If your table is partitioned, filter the partition column directly, and see BigQuery PARTITION BY for how pruning actually works.

Skip the syntax and just ask the question

Choosing between IF and CASE, remembering that a NULL condition falls through to the else branch, and knowing that COUNTIF exists at all are the kind of details that make hand-written SQL slow even for people who are fluent in it. Agentsql connects read-only to BigQuery, turns a plain-English question into correct GoogleSQL with the conditional and null handling worked out, runs it, and shows you the query so you can confirm the branches land where you expect before anyone acts on the number. See the BigQuery integration for how the read-only connection works and the SQL query generator for how the translation happens, then see how it works and ask BigQuery a question in plain English.