NULL Keys Silently Break BigQuery Joins and MERGEs

I once lost the better part of a day to a table that would not stop growing. It was a cohort table for a mobile app, keyed on four columns: install_date, platform, country, and network. The pipeline merged fresh numbers into it every few hours, and every run left a few more rows than the one before. Not a flood, just a slow drip of duplicates that quietly threw off every count built on top of it.

I checked the obvious things first. The MERGE condition matched all four key columns, the source query looked clean, the keys were right there in the ON clause. Nothing was wrong with the SQL. It took hours of narrowing down rows to spot what the duplicates had in common: country was NULL. For a slice of users we could not resolve a country, so the column came through empty, and that single NULL was enough to keep the four-column key from matching its earlier copy. Instead of updating the existing row, the MERGE inserted a fresh one. Every run.

The bug was the NULL. It comes down to a rule everyone learns and then forgets: a NULL is never equal to another NULL, not even to itself. One nullable column anywhere in a join or merge key is all it takes. The composite key only slowed me down: with three of the four columns always populated, the key looked fine, so I went hunting everywhere except the place it was breaking.

I will start with the join, since that is where the problem lives, then trace it downstream to the duplicates I just described. After that, the fix, and why the obvious null-safe operator (IS NOT DISTINCT FROM) is the wrong one to reach for on BigQuery. Numbers and full benchmark at the end.

See it happen

You can reproduce the whole thing with two CTEs and no tables. Paste this into BigQuery. Both target and source hold the same two logical rows: one with user_id = 1, one with a NULL user_id. A full outer join on equality should line each pair up into a single row.

WITH target AS (
  SELECT 1 AS user_id, 'existing' AS state UNION ALL
  SELECT NULL,         'existing'
),
source AS (
  SELECT 1 AS user_id, 'incoming' AS state UNION ALL
  SELECT NULL,         'incoming'
)
SELECT
  t.user_id AS target_id,
  s.user_id AS source_id,
  t.state   AS in_target,
  s.state   AS in_source
FROM target t
FULL OUTER JOIN source s ON t.user_id = s.user_id
ORDER BY target_id, source_id;

The user_id = 1 pair reconciled into one row, existing next to incoming, which is what you asked the join to do. The NULL user did not, even though it sits on both sides. It came back as two half-empty rows, one from each table, because the join never treated the two NULLs as the same key.

Three rows of dummy data and the join already cannot hold a NULL together. At real scale the same failure surfaces as dropped rows, fan-out, or duplicates, depending on what the join feeds into.

NULL = NULL is not TRUE

SQL uses three-valued logic. A comparison can be TRUE, FALSE, or NULL (unknown). When either side of = is NULL, the result is NULL, not TRUE:

SELECT
  1 = 1        AS a,   -- TRUE
  1 = 2        AS b,   -- FALSE
  NULL = NULL  AS c,   -- NULL, not TRUE
  NULL = 1     AS d;   -- NULL

A join keeps a pair of rows only when the ON condition evaluates to TRUE. NULL is not TRUE, so any row whose key is NULL on either side drops out of the match. This is plain ANSI SQL, the same in Postgres, Snowflake, and everywhere else, and it holds whether you write a JOIN or a MERGE, since a MERGE condition is a join condition wearing a different hat.

What each operation then does with those unmatched rows is where the money goes.

Where it bites

The unmatched rows are the root cause. What you actually notice happens downstream, and it looks different depending on what the join is feeding.

Plain joins lose or multiply rows. An inner join keeps only matched rows, so the NULL-keyed row just vanishes. A left join keeps it but fills the right-hand columns with NULL, and now every SUM or AVG over those columns is quietly off. If both sides carry several NULL keys and you later try to line them up null-safely, they do not pair one to one either, they fan out into a cross product.

Incremental models duplicate rows. This is the one that corrupts a table slowly enough that nobody catches it. A MERGE's ON clause decides which source rows are MATCHED (update the target) and which are NOT MATCHED BY TARGET (insert as new). A source row with a NULL key can never match an existing target row, so on every run it falls into the NOT MATCHED branch and gets inserted again. The same logical row piles up once per run, and there is no single moment where the count looks obviously wrong.

The same thing inside a MERGE, runnable as-is. A target with two rows, a source carrying those same rows with a new payload, plain equality on the key (temp tables, since a MERGE needs a real target, not a CTE):

CREATE TEMP TABLE target AS
SELECT * FROM UNNEST([
  STRUCT(CAST(NULL AS INT64) AS user_id, 'v1' AS payload),
  STRUCT(1                   AS user_id, 'v1' AS payload)
]);

CREATE TEMP TABLE source AS
SELECT * FROM UNNEST([
  STRUCT(CAST(NULL AS INT64) AS user_id, 'v2' AS payload),
  STRUCT(1                   AS user_id, 'v2' AS payload)
]);

MERGE target t
USING source s
ON t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET payload = s.payload
WHEN NOT MATCHED THEN INSERT (user_id, payload) VALUES (s.user_id, s.payload);

SELECT user_id, payload FROM target ORDER BY user_id, payload;

Two rows went in, three came out. The user_id = 1 row matched and updated in place, v1 became v2. The NULL row never matched its existing copy, so instead of updating it, the MERGE inserted a second one. Run this a third and fourth time and you get a fourth and fifth NULL row. This is what a null key does to an incremental model that reruns every hour.

I used one key column here to keep it short. It works the same for a composite key: a NULL in any one column breaks the match, which is all it took to duplicate that cohort table.

IS NOT DISTINCT FROM fixes it. Or does it?

SQL has a null-safe equality operator built for exactly this. IS NOT DISTINCT FROM treats two NULLs as equal and a NULL and a value as unequal:

SELECT
  (NULL IS NOT DISTINCT FROM NULL) AS a,   -- TRUE
  (NULL IS NOT DISTINCT FROM 1)    AS b,    -- FALSE
  (1 IS NOT DISTINCT FROM 1)       AS c;    -- TRUE

So I did the obvious thing. Swap the equality for it, ship it, and the duplicates stop:

-- null-safe: the duplicates are gone
ON t.user_id IS NOT DISTINCT FROM s.user_id

And they were gone. Then the pipeline that used to finish in a couple of minutes started taking forever. Same data, same row counts, but every model that touched this join crawled, and the slot usage on them went through the roof.

The reason is that on BigQuery IS NOT DISTINCT FROM quietly turns a hash join into a cross join. BigQuery can only run a hash join when the condition is a field equality, a = b: it reads a hash key off each side and matches everything in one pass. To the planner, IS NOT DISTINCT FROM is not a field equality, so it cannot build a hash key from it and falls back to comparing every row on one side against every row on the other. That is a cross join with a filter on top, and it is quadratic. On a table of any real size, that is the difference between a job that finishes and one that does not.

What the cross join costs

I benchmarked this on tables with no NULLs at all, so the only variable is the operator. A plain inner join, counting the matched rows:

The equality join plans as INNER HASH JOIN ALL WITH ALL ON $1 = $10. The IS NOT DISTINCT FROM join plans as CROSS EACH WITH EACH. Same data, same answer, between 1,800 and 3,900 times the compute, and there is not one NULL in either table. You pay for the cross join even when nothing is null and the null-safety you asked for never does a thing.

Inside a MERGE the effect is the same but shows up differently. On a 100k target the null-safe MERGE burned about 227x the slots of the equality MERGE. Push it to a 500k target and it no longer runs at all:

Query exceeded resource limits. This query used 75767 CPU seconds
but would charge only 28M Analysis bytes. This exceeds the ratio
supported by the on-demand pricing model.

On on-demand pricing, BigQuery kills a query whose compute is wildly out of proportion to the bytes it scans. A cross join over half a million rows hits that ceiling. The correctness fix made the model unrunnable.

It will not do a full outer join at all

There is a second, smaller problem worth knowing about. In a full outer join, IS NOT DISTINCT FROM never even gets the chance to be slow. BigQuery rejects it outright:

SELECT COUNT(*)
FROM target t
FULL OUTER JOIN source s
  ON t.user_id IS NOT DISTINCT FROM s.user_id;

400 FULL OUTER JOIN cannot be used without a condition that is
an equality of fields from both sides of the join.

A full outer join has no cross-join plan to degrade to, so rather than run something quadratic BigQuery refuses. The error spells out what the planner wants: an equality of fields. IS NOT DISTINCT FROM is not one, which is exactly why it fell to a cross join everywhere else.

What works instead: OR the null case yourself

There is a form that is null-safe and still a plain field equality on one branch, so the planner can extract hash keys from it:

ON (t.user_id = s.user_id
    OR (t.user_id IS NULL AND s.user_id IS NULL))

Logically this is identical to IS NOT DISTINCT FROM: keys match when they are equal, or when both are NULL. The difference is that BigQuery's optimizer recognizes the = branch, normalizes the condition into two hash keys (the value, and a "is null" flag), and plans a hash join:

INNER HASH JOIN ALL WITH ALL ON $50 = $60, $51 = $61

Across every scenario, join type, and table size I tried, the OR-form behaved like plain equality and never dropped to a cross join. Where its numbers land just below equality in the table, that is noise, not a real edge, since the two overlap run to run. The column to watch is the last one.

Unlike IS NOT DISTINCT FROM, the OR-form also satisfies the full-outer-join requirement, because it contains a real field equality. It is the only single-expression null-safe condition in this list that BigQuery will accept everywhere and still plan as a hash join.

But what if you really do want to match NULLs?

Sometimes a NULL is a value you mean to keep. A composite key with a "not applicable" column, a segment where NULL means "global", a dimension that is genuinely unknown but still has to line up on both sides. Then you do want NULL to match NULL, and the OR-form gives you that at the cost of an ordinary equality join, per the numbers above. The one operator to avoid is IS NOT DISTINCT FROM, which buys you the identical result for orders of magnitude more compute.

One caveat, and it comes from what NULL means rather than from anything BigQuery does. Nulls are not unique. Match null-to-null and every null on one side matches every null on the other. In a plain join that is the full cross product of the two null groups, so a hundred nulls on each side turn into ten thousand rows. A MERGE is stricter: if the source has more than one null-keyed row, one null target row matches all of them at once and BigQuery raises

UPDATE/MERGE must match at most one source row for each target row

So matching nulls is fine, and fast, as long as the null key is effectively unique on the source side. If it can appear more than once, dedupe the source down to one row per null group first (the same way you would already be deduping any key that can repeat), then match with the OR-form.

If the nulls are not meaningful, if a key that should never be null leaked through from upstream, then the cheapest fix is to not match on them at all. Filter them out and handle them on their own terms:

MERGE target AS t
USING (SELECT * FROM source WHERE user_id IS NOT NULL) AS s
ON t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...;

Then decide, deliberately, what happens to the user_id IS NULL rows: reject them, quarantine them for inspection, or give them a deterministic surrogate key. In the benchmark this filtered-equality path was the fastest of all, because it does the least work. But that is a choice about your data, not a workaround for the operator. If you want the nulls in the join, keep them, and use the OR-form.

What to reach for

• When you want to match NULLs: use the OR-form, key_a = key_b OR (key_a IS NULL AND key_b IS NULL). Correct, stays a hash join, and works in a MERGE and in a full outer join. Dedupe the source if a null key can appear more than once.
• When the NULLs should not be there: filter WHERE key IS NOT NULL out of the source and handle those rows explicitly. Fastest, and it forces you to say what a null key actually means.
• Never on BigQuery: IS NOT DISTINCT FROM in a join or MERGE condition. Correct but plans as a cross join, costs orders of magnitude more, and fails outright at moderate scale on on-demand pricing.

The general rule outlives this one bug: on BigQuery a join is only cheap when the condition reduces to a field equality. Anything the planner cannot boil down to a = b can land you in a cross join. So the shape of your join condition is also, whether you meant it or not, a choice of join algorithm.

How I ran the benchmark

All measurements are BigQuery on-demand, US multi-region, slot_ms reported from the job statistics (job.slot_millis), plan algorithms read from the query plan stages. The numbers in the tables are single representative runs, so treat small differences as noise. slot_ms varies run to run by a few tens of percent at these sizes; across repeated runs of the medium join, equality ranged 164 to 351 slot_ms and the OR-form 143 to 306, fully overlapping. The IS NOT DISTINCT FROM gap is three to four orders of magnitude, far outside any run-to-run variance, which is the only comparison the post leans on.

Tables were generated with an INT64 user_id key, clustered by user_id, with a controllable fraction of NULL keys. Source tables used 50% overlap with existing target keys and 50% new keys. Scenarios ranged from 100k to 5M target rows. The "0 NULLs" rows above use tables with no NULL keys at all, isolating the operator from any null-handling work.

Join variants compared (inner and full outer):

-- equality baseline
ON t.user_id = s.user_id
-- null-safe, cross-join trap
ON t.user_id IS NOT DISTINCT FROM s.user_id
-- null-safe, stays a hash join
ON (t.user_id = s.user_id OR (t.user_id IS NULL AND s.user_id IS NULL))

MERGE variants additionally included filtering NULLs out of the source (USING (SELECT * FROM source WHERE user_id IS NOT NULL)) and a two-statement split (equality MERGE for non-null keys, separate insert for NULL keys).