# 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.

Source: https://docs.querylab.io/bigquery/null-handling/

Updated: 2026-10-14

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:

**comparing with a literal NULL**

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

BigQuery rejects this query:

```text
Operands of = cannot be literal NULL at [3:11]
```

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`:

**IS NULL finds the missing value**

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

Result in BigQuery (1 row):

| x |
| --- |
| 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`:

**= vs IS NOT DISTINCT FROM**

```sql
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
```

Result in BigQuery (3 rows):

| a | b | equals | same_value |
| --- | --- | --- | --- |
| 1 | 1 | true | true |
| 1 | NULL | NULL | false |
| NULL | NULL | NULL | true |

`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:

**NOT IN against a list that holds a NULL**

```sql
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)
```

Result in BigQuery (0 rows):

| customer_id |
| --- |

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

**NOT EXISTS ignores the NULL**

```sql
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
```

Result in BigQuery (2 rows):

| customer_id |
| --- |
| 1 |
| 3 |

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:

**AVG over 10, NULL and 20**

```sql
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
```

Result in BigQuery (1 row):

| total | average | biggest | average_with_zeros |
| --- | --- | --- | --- |
| 30 | 15.0 | 20 | 10.0 |

`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:

**returning [1, NULL, 3]**

```sql
SELECT [1, NULL, 3] AS numbers
```

BigQuery rejects this query:

```text
Array cannot have a null element; error in writing field 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`:

**ARRAY_AGG(x IGNORE NULLS)**

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

Result in BigQuery (1 row):

| without_nulls | counted | all_rows |
| --- | --- | --- |
| [1, 3] | 2 | 3 |

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 nulls the result

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

**building a full name**

```sql
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
```

Result in BigQuery (2 rows):

| with_operator | with_concat | with_ifnull |
| --- | --- | --- |
| Ada Lovelace | Ada Lovelace | Ada Lovelace |
| NULL | NULL | Plato  |

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

## Functions for NULL

**COALESCE, IFNULL and NULLIF**

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

Result in BigQuery (1 row):

| first_non_null | ifnull_result | nullif_result |
| --- | --- | --- |
| third | 0 | NULL |

- `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 NOT NULL` ([code quality](https://docs.querylab.io/writing-sql/code-quality/#quick-fixes)).

See what NOT IN does when the list holds a NULL. 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:

- [Operators](https://cloud.google.com/bigquery/docs/reference/standard-sql/operators)

- [Conditional expressions](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions)

- [Data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)