NULL in BigQuery

BigQuery rejects x = NULL with an error instead of returning no rows, refuses NULL elements in arrays it writes, and has IS NOT DISTINCT FROM for NULL-safe equality. How NULL behaves in comparisons, NOT IN, aggregates and strings.

NULL means “no value”. Any comparison with NULL is neither true nor false but NULL, and WHERE keeps only rows where the condition is true. That much is standard SQL. BigQuery adds two rules of its own: it refuses to compare with a literal NULL, and it refuses to write an array that contains a NULL.

x = NULL is an error

In most databases WHERE x = NULL silently returns no rows. BigQuery stops the query instead:

SELECT x
FROM UNNEST([1, NULL, 3]) AS x
WHERE x = NULL

The same applies to !=, <, +, ||, LIKE and the other comparison and arithmetic operators: when one side is the literal NULL, the query fails. IN, BETWEEN, AND and OR accept it. Use IS NULL and IS NOT NULL:

SELECT x
FROM UNNEST([1, NULL, 3]) AS x
WHERE x IS NULL

A NULL that comes from a column or a function is allowed and behaves the standard way. Only the literal is rejected, because writing it is always a mistake.

Comparing two columns that may be NULL

a = b is NULL when either side is NULL, including when both are. To treat two NULLs as equal, use IS NOT DISTINCT FROM:

WITH t AS (
  SELECT 1 AS a, 1 AS b UNION ALL
  SELECT 1, NULL UNION ALL
  SELECT NULL, NULL
)
SELECT
  a,
  b,
  a = b AS equals,
  a IS NOT DISTINCT FROM b AS same_value
FROM t

IS DISTINCT FROM is the opposite. These are the operators to use in a MERGE condition or a join that should match missing values.

NOT IN with a NULL returns nothing

If the subquery behind NOT IN returns a single NULL, the whole condition is NULL for every row, and the query returns no rows. Here customer 2 is blocked and one blocked row has no ID, so you might expect customers 1 and 3:

WITH orders AS (
  SELECT 1 AS customer_id UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
),
blocked AS (
  SELECT 2 AS customer_id UNION ALL
  SELECT NULL
)
SELECT customer_id
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blocked)

NOT EXISTS doesn’t have this problem, because a NULL never matches inside the subquery:

WITH orders AS (
  SELECT 1 AS customer_id UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
),
blocked AS (
  SELECT 2 AS customer_id UNION ALL
  SELECT NULL
)
SELECT customer_id
FROM orders AS o
WHERE NOT EXISTS (
  SELECT 1 FROM blocked AS b WHERE b.customer_id = o.customer_id
)
ORDER BY customer_id

Prefer NOT EXISTS, or filter the NULLs out of the subquery with WHERE customer_id IS NOT NULL.

Aggregates skip NULL

SUM, AVG, MAX, MIN and COUNT(column) ignore NULLs. AVG divides by the number of non-NULL values, which is not always what you want:

SELECT
  SUM(x) AS total,
  AVG(x) AS average,
  MAX(x) AS biggest,
  SUM(IFNULL(x, 0)) / COUNT(*) AS average_with_zeros
FROM UNNEST([10, NULL, 20]) AS x

AVG returns 15 because it averages two values. If a missing value should count as zero, replace it first. COUNT(*) counts rows, COUNT(x) counts non-NULL values of x.

Arrays can’t hold NULL

A BigQuery array can’t contain a NULL element when it is written to a result or a table. The query fails as soon as such an array would be returned:

SELECT [1, NULL, 3] AS numbers

An array that holds a NULL can exist inside a query, for example inside ARRAY_LENGTH or UNNEST, but not in its output. ARRAY_AGG would produce one from a column with NULLs, so add IGNORE NULLS:

SELECT
  ARRAY_AGG(x IGNORE NULLS) AS without_nulls,
  COUNT(x) AS counted,
  COUNT(*) AS all_rows
FROM UNNEST([1, NULL, 3]) AS x

A NULL array is a different thing from an array that holds a NULL. A whole array column can be NULL. In query results BigQuery returns a NULL array as an empty array [].

Strings: one NULL makes the whole result NULL

Both || and CONCAT return NULL if any part is NULL, so one missing last name blanks out the full name:

WITH people AS (
  SELECT 'Ada' AS first_name, 'Lovelace' AS last_name UNION ALL
  SELECT 'Plato', NULL
)
SELECT
  first_name || ' ' || last_name AS with_operator,
  CONCAT(first_name, ' ', last_name) AS with_concat,
  CONCAT(first_name, ' ', IFNULL(last_name, '')) AS with_ifnull
FROM people

Wrap the parts that may be missing in IFNULL(part, '').

Functions for NULL

SELECT
  COALESCE(NULL, NULL, 'third') AS first_non_null,
  IFNULL(NULL, 0) AS ifnull_result,
  NULLIF(5, 5) AS nullif_result

• COALESCE(a, b, c) returns the first argument that is not NULL.
• IFNULL(a, b) is COALESCE with two arguments.
• NULLIF(a, b) returns NULL when a = b, otherwise a. x / NULLIF(y, 0) avoids a division-by-zero error, and so does SAFE_DIVIDE(x, y).

In Querylab.io, comparing with a literal NULL is underlined as an error while you type, before BigQuery gets to reject it. The quick fix replaces it with IS NULL or IS DISTINCT FROM (code quality).