BigQuery CASE WHEN: How to Write Conditional Logic in SQL

BigQuery CASE WHEN evaluates a list of conditions in order and returns the value for the first one that is true, so it is how you write if-then-else logic inside a SQL query. The shape is CASE WHEN condition THEN result ... ELSE fallback END. You use it to label rows, bucket numbers into ranges, and count or sum only the rows that meet a condition. Below is the syntax, how to test multiple conditions, how NULL behaves, and how CASE compares to the shorter IF function.

CASE WHEN syntax

BigQuery supports two forms. The searched form tests a full boolean condition in every WHEN and is the one you will use most. The simple form compares one expression against a list of values.

-- searched CASE: each WHEN is its own condition
SELECT
  order_id,
  CASE
    WHEN total >= 500 THEN 'large'
    WHEN total >= 100 THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM sales.orders

Order matters. BigQuery checks the WHEN clauses top to bottom and stops at the first match, so the >= 500 test has to come before >= 100. If you list them the other way round, every large order also passes the 100 test first and gets mislabeled "medium". If no WHEN matches and there is no ELSE, the result is NULL.

Testing multiple conditions in one WHEN

A single WHEN can combine conditions with AND and OR, so you do not need a separate branch for every combination.

SELECT
  customer_id,
  CASE
    WHEN plan = 'pro' AND seats > 10 THEN 'enterprise-lean'
    WHEN plan = 'pro' OR plan = 'team' THEN 'paid'
    ELSE 'free'
  END AS segment
FROM accounts

Wrap OR groups in parentheses when you mix them with AND, because AND binds tighter. WHEN a AND (b OR c) is not the same condition as WHEN a AND b OR c, and the second one rarely means what you intended.

CASE WHEN with NULL

NULL needs its own handling, because a comparison against NULL is never true, it is unknown. WHEN status = NULL will never match anything. Use IS NULL instead.

SELECT
  user_id,
  CASE
    WHEN last_login IS NULL THEN 'never logged in'
    WHEN last_login < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) THEN 'dormant'
    ELSE 'active'
  END AS status
FROM users

Put the IS NULL branch first when a NULL should win over the later numeric tests, for the same top-to-bottom reason as before. If all you want is a default for missing values, COALESCE(col, 'default') or IFNULL(col, 'default') is shorter than a CASE.

CASE inside SUM and COUNT for conditional aggregation

The most powerful use of CASE is counting or summing a subset of rows without a separate query. You put a CASE inside an aggregate and give each condition a 1 or the value you want, and 0 or NULL otherwise.

SELECT
  DATE_TRUNC(created_at, MONTH) AS month,
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds,
  SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_revenue
FROM sales.orders
GROUP BY month
ORDER BY month

This is how you turn a single grouped query into a small report with several conditional columns side by side. It is also the manual way to pivot rows into columns, which our note on rotating rows to columns in BigQuery covers with the dedicated PIVOT operator.

CASE WHEN vs IF: which to use

BigQuery also has an IF(condition, true_result, false_result) function. It is shorter for a single either-or test. CASE is the right choice the moment you have more than two outcomes.

A practical example: bucketing spend

Bucketing is the everyday job CASE was made for. Say you want to group transactions into spend bands for a finance review.

SELECT
  vendor,
  CASE
    WHEN amount >= 10000 THEN 'review required'
    WHEN amount >= 1000 THEN 'manager approval'
    ELSE 'auto-approved'
  END AS approval_band,
  COUNT(*) AS txns
FROM finance.payments
GROUP BY vendor, approval_band

This is the same labelling logic an expense tool applies when it categorizes every transaction automatically, just written out by hand so you control the thresholds. Once the bands are defined once, every report that groups by approval_band stays consistent.

Ask it in plain English instead

You do not have to hand-write CASE ladders to segment your data. Agentsql connects read-only to BigQuery, turns a plain-English question like "count refunded versus shipped orders by month" into the correct Standard SQL (conditional SUMs and all), runs it, and shows you the query it wrote so you can check the thresholds. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.