How to Handle NULL Values in BigQuery

Galaxy Glossary

How do I handle NULL values in BigQuery?

BigQuery offers functions like IFNULL, COALESCE, NULLIF, and SAFE_ operators to replace, detect, or safeguard against NULL values during queries.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is the fastest way to replace NULLs in BigQuery?

Use IFNULL(expression, replacement) when you need a single fallback value. It evaluates the expression once and substitutes the replacement only if the result is NULL, keeping queries succinct and performant.

How can I return the first non-NULL value across multiple columns?

Apply COALESCE(col1, col2, col3, ...). BigQuery scans each argument in order and returns the first non-NULL. This is ideal for hierarchical data like primary, secondary, and tertiary contact emails.

When should I use NULLIF?

Choose NULLIF(expr1, expr2) to convert specific sentinel values (e.g., 0, empty strings) into NULL. This simplifies downstream aggregation logic by treating placeholders as true NULLs.

Example—standardizing missing prices

SELECT product_id, NULLIF(price, 0) AS price turns zero prices into NULL, ensuring AVG(price) ignores them.

How do SAFE_ functions prevent errors with NULL?

Functions like SAFE_DIVIDE(numerator, denominator) return NULL instead of raising a division-by-zero error. They keep analytic pipelines from failing while still signaling invalid math.

Best practice: always filter aggregates

Wrap metrics in FILTER (WHERE col IS NOT NULL) or rely on BigQuery’s default NULL-ignoring behavior in AVG, SUM, and COUNT(col) to avoid inflated counts.

How do I detect NULL versus empty string?

Use IS NULL and IS NOT NULL for NULLs and ='' for empty strings. Combining both checks ensures complete data quality scans.

Practical NULL handling workflow

Convert placeholders to NULL with NULLIF.
Replace required fields with defaults using IFNULL or COALESCE.
Guard math operations via SAFE_* functions.
Filter or ignore NULLs during aggregations.

Why How to Handle NULL Values in BigQuery is important

How to Handle NULL Values in BigQuery Example Usage


-- Calculate average order value excluding zero or NULL totals
WITH CleanOrders AS (
  SELECT
    id,
    NULLIF(total_amount, 0) AS total_amount
  FROM `project.dataset.Orders`
)
SELECT
  AVG(total_amount) AS avg_order_value
FROM CleanOrders;

How to Handle NULL Values in BigQuery Syntax


-- Basic NULL-handling syntax in BigQuery
IFNULL(expression, replacement)
COALESCE(expr1, expr2, ...)
NULLIF(expr1, expr2)
SAFE_DIVIDE(numerator, denominator)

-- Ecommerce context examples
-- 1. Replace missing customer names with 'Guest'
SELECT IFNULL(name, 'Guest') AS customer_name
FROM `project.dataset.Customers`;

-- 2. Fall back to product stock, then default 0
SELECT COALESCE(stock, 0) AS available_stock
FROM `project.dataset.Products`;

-- 3. Turn zero totals into NULL for cleaner averages
SELECT NULLIF(total_amount, 0) AS total_amount
FROM `project.dataset.Orders`;

Common Mistakes

Using <code>= NULL</code> for comparisons. This always returns FALSE because NULL is not a value. Fix by using <code>IS NULL</code> or <code>IS NOT NULL</code>.
Applying IFNULL across many columns instead of COALESCE. IFNULL handles only one expression; using it repeatedly slows queries. Replace chains with a single COALESCE call.

Frequently Asked Questions (FAQs)

Does COUNT(*) ignore NULLs?

Yes. COUNT(*) counts rows, while COUNT(column) ignores NULLs in that column.

Is COALESCE faster than IFNULL?

For one expression both are similar, but COALESCE scales better for multiple fallbacks by avoiding nested IFNULL calls.

Can I set a session-wide NULL replacement?

No. NULL handling is defined per query in BigQuery; use view layers or default values to enforce consistency.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie