Semi-Joins and Anti-Joins in BigQuery: 10 Code-Along Examples

The semi-join and anti-join article sets the mental model that makes everything else click: these are filters, not merges. A regular join stitches columns from two tables together and can multiply rows when one key matches many; a semi-join and an anti-join do something narrower and safer, keeping each left-table row exactly once based on whether a match exists on the right, and adding no columns from it. A semi-join keeps the rows that match. An anti-join keeps the rows that do not. This workbook builds both, shows the three ways to write each, and spends real time on the NOT IN null trap, which is the single most expensive mistake in this whole area.

Setup: run this once

CREATE SCHEMA IF NOT EXISTS scratch;
-- customers: note 105 and 106 have never purchased
CREATE OR REPLACE TABLE scratch.customers AS
SELECT * FROM UNNEST([
  STRUCT(101 AS customer_id, 'Ana'  AS name, 'UK' AS country),
  (102, 'Ben',  'UK'),
  (103, 'Cara', 'DE'),
  (104, 'Dan',  'US'),
  (105, 'Eve',  'US'),
  (106, 'Finn', 'DE')
]);
-- purchases: customers 101-104 only, plus one guest checkout with a NULL customer_id
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 101 AS customer_id, 'Books' AS category, 40 AS amount, DATE '2026-01-01' AS purchase_date, 'complete' AS status),
  (2,  101, 'Books',  55,  DATE '2026-01-03', 'complete'),
  (3,  101, 'Games',  90,  DATE '2026-01-06', 'complete'),
  (4,  102, 'Books',  30,  DATE '2026-01-02', 'complete'),
  (5,  102, 'Games', 120,  DATE '2026-01-04', 'complete'),
  (6,  102, 'Games',  75,  DATE '2026-01-09', 'refunded'),
  (7,  103, 'Music',  20,  DATE '2026-01-01', 'complete'),
  (8,  103, 'Music',  60,  DATE '2026-01-05', 'complete'),
  (9,  103, 'Books',  45,  DATE '2026-01-07', 'complete'),
  (10, 104, 'Games', 200,  DATE '2026-01-08', 'complete'),
  (11, 104, 'Music',  35,  DATE '2026-01-10', 'complete'),
  (12, 104, 'Books',  80,  DATE '2026-01-11', 'complete'),
  (13, CAST(NULL AS INT64), 'Games', 50, DATE '2026-01-12', 'complete')  -- guest checkout
]);

Six customers, two of whom (Eve and Finn) have never bought anything, and thirteen purchases, the last of which is a guest checkout with no customer_id. That single null row is what makes Example 5 fail, so leave it in.

1. The mental model: a filter, not a merge

Before the new patterns, see the problem they solve. A plain inner join to find “customers who have purchased” multiplies the customer rows, once per matching purchase, which is almost never what you want.

-- the WRONG tool: an inner join fans out
SELECT c.customer_id, c.name
FROM scratch.customers AS c
JOIN scratch.purchases AS p ON p.customer_id = c.customer_id
ORDER BY c.customer_id;

customer_id | name
------------+------
101         | Ana
101         | Ana
101         | Ana
102         | Ben
102         | Ben
102         | Ben
103         | Cara
103         | Cara
103         | Cara
104         | Dan
104         | Dan
104         | Dan

Ana appears three times and Ben three times, because the join produces one row per matching purchase. To get a clean list you would have to bolt on SELECT DISTINCT, and the moment you also select a purchase column the deduplication silently stops working. This fan-out is the whole reason semi-joins exist: you wanted to filter the customer list, not join it to the order detail, and the next two examples do exactly that.

2. Semi-join with EXISTS

EXISTS is a correlated subquery that asks, for each customer, “is there at least one matching purchase?” It returns each customer at most once and adds nothing from the purchases table.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE EXISTS (
  SELECT 1
  FROM scratch.purchases AS p
  WHERE p.customer_id = c.customer_id
)
ORDER BY c.customer_id;

customer_id | name
------------+------
101         | Ana
102         | Ben
103         | Cara
104         | Dan

This returns Ana, Ben, Cara, and Dan once each, with no fan-out and no DISTINCT needed. The SELECT 1 inside is deliberate and idiomatic: EXISTS only cares whether the subquery produces any row at all, not what it contains, so there is no point naming a real column. The correlation is the WHERE p.customer_id = c.customer_id line, which links the inner query to the current outer customer, and it is what makes this a semi-join rather than a blanket membership test.

3. Semi-join with IN

IN expresses the same semi-join as a membership test: keep the customer if their id appears in the set of ids that have purchased. It reads more simply than EXISTS for straightforward cases.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE c.customer_id IN (
  SELECT customer_id
  FROM scratch.purchases
)
ORDER BY c.customer_id;

customer_id | name
------------+------
101         | Ana
102         | Ben
103         | Cara
104         | Dan

The result is identical to Example 2. The difference is stylistic and, on large tables, a matter of the query plan: EXISTSoften optimises better because the engine can stop at the first match and exploit the correlation, while IN is cleaner to read on a small or medium set of values. For a semi-join the two are interchangeable in meaning, and the article’s advice is to pick whichever reads more clearly, leaning on EXISTS when the tables are large. The crucial asymmetry appears only when you negate them, which is the next lesson.

4. Anti-join with NOT EXISTS

Flip EXISTS to NOT EXISTS and the semi-join becomes an anti-join: keep the customers for whom no matching purchase exists. This is the reliable way to find “who has never done X”.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM scratch.purchases AS p
  WHERE p.customer_id = c.customer_id
)
ORDER BY c.customer_id;

customer_id | name
------------+------
105         | Eve
106         | Finn

This correctly returns Eve and Finn, the two customers with no purchases. Anti-joins answer some of the most valuable business questions there are: customers who never bought, products never sold, accounts with no login. NOT EXISTS is the recommended way to write every one of them, for a reason the next example makes painfully concrete: it is immune to the null trap that quietly breaks the more obvious NOT IN.

5. The NOT IN null trap

NOT IN looks like the natural way to write an anti-join, and it is a landmine. If the subquery’s column contains even one null, NOT IN returns no rows at all, silently.

-- LOOKS right, returns NOTHING because of the guest checkout's NULL customer_id
SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE c.customer_id NOT IN (
  SELECT customer_id
  FROM scratch.purchases
)
ORDER BY c.customer_id;

(0 rows)

You expect Eve and Finn, and you get an empty result. The cause is three-valued logic. NOT IN is evaluated as “not equal to every value in the list”, and comparing anything to the null from the guest checkout yields unknown rather than false, so the engine can never confirm that a customer is absent from the list, and every row is rejected. The failure is silent, which is what makes it dangerous: no error, just a wrong and empty answer. This is why the article, and every careful practitioner, reaches for NOT EXISTS instead.

6. Making NOT IN safe, if you must use it

If you are wedded to NOT IN, you have to exclude the nulls yourself. Adding WHERE ... IS NOT NULL inside the subquery restores the correct result, but notice how much easier NOT EXISTS was.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE c.customer_id NOT IN (
  SELECT customer_id
  FROM scratch.purchases
  WHERE customer_id IS NOT NULL      -- the guard that rescues NOT IN
)
ORDER BY c.customer_id;

customer_id | name
------------+------
105         | Eve
106         | Finn

With the null filtered out of the subquery, this finally returns Eve and Finn. The lesson is not “here is how to use NOT IN“, it is “look how much vigilance NOT IN demands”. You must remember the guard on every anti-join, forever, and forgetting it fails silently rather than loudly. NOT EXISTS needs no guard because its logic never compares against the null in the first place, which is why it is the default and NOT IN is the exception you should be able to justify.

7. The LEFT JOIN / IS NULL anti-join

There is a third way to write an anti-join, older and still common: a left join that keeps all left rows, then a filter for the rows where the right side came back null, meaning no match was found.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
LEFT JOIN scratch.purchases AS p
  ON p.customer_id = c.customer_id
WHERE p.customer_id IS NULL
ORDER BY c.customer_id;

customer_id | name
------------+------
105         | Eve
106         | Finn

This also returns Eve and Finn. The LEFT JOIN attaches purchases where they exist and fills nulls where they do not, so filtering for p.customer_id IS NULL keeps exactly the unmatched customers. It works, and you will meet it constantly in older code, but it asks the reader to reason about join semantics and null-filled rows to see that it is an anti-join at all, whereas NOT EXISTS says what it means. Prefer NOT EXISTS for clarity; recognise this pattern so you can read and safely refactor the queries that use it.

8. Did X but not Y: behavioural segmentation

The most powerful use of anti-joins is a self anti-join against the same table, which carves out “customers who did one thing but not another”. Here: bought Books but never bought Games.

SELECT DISTINCT p.customer_id
FROM scratch.purchases AS p
WHERE p.category = 'Books'
  AND NOT EXISTS (
    SELECT 1
    FROM scratch.purchases AS pg
    WHERE pg.customer_id = p.customer_id
      AND pg.category = 'Games'
  )
ORDER BY p.customer_id;

customer_id
-----------
103

This returns customer 103 (Cara), who bought Books and Music but never Games, while excluding Ana, Ben, and Dan, who each bought Games as well. The structure is the key idea: the outer query finds the “did X” rows, and the correlated NOT EXISTS removes anyone who also “did Y”, using a second alias on the same table. This “did X but not Y” segment is the backbone of churn signals, cross-sell targeting, and funnel analysis, and it is almost impossible to express cleanly without an anti-join.

9. Semi-join with a filter inside the subquery

Filtering inside the subquery narrows what counts as a match, which is both a correctness tool and a performance one. Here: customers with at least one completed purchase over £100.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE EXISTS (
  SELECT 1
  FROM scratch.purchases AS p
  WHERE p.customer_id = c.customer_id
    AND p.status = 'complete'
    AND p.amount > 100
)
ORDER BY c.customer_id;

customer_id | name
------------+------
102         | Ben
104         | Dan

The extra conditions inside EXISTS change the meaning of “match”: now only completed purchases over £100 qualify. Ben’s £120 Games order is complete and clears the bar, and Dan’s £200 order does too, so both appear. Ana and Cara are filtered out, because their largest completed purchases (£90 and £60) never reach £100, and Ben’s refunded £75 order would not have counted even if it had been large enough. Filtering early inside the subquery is also the article’s headline performance tip, because it shrinks the set the engine must scan before the correlation runs, and on partitioned tables it lets BigQuery prune whole partitions.

10. Combining semi and anti in one segment

Real segments usually stack conditions. This final query finds a genuinely useful cohort: UK customers who have purchased, but have never had a refund, the loyal-and-clean segment you might reward.

SELECT c.customer_id, c.name
FROM scratch.customers AS c
WHERE c.country = 'UK'
  AND EXISTS (                                   -- has purchased
    SELECT 1 FROM scratch.purchases AS p
    WHERE p.customer_id = c.customer_id
  )
  AND NOT EXISTS (                               -- but never a refund
    SELECT 1 FROM scratch.purchases AS pr
    WHERE pr.customer_id = c.customer_id
      AND pr.status = 'refunded'
  )
ORDER BY c.customer_id;

customer_id | name
------------+------
101         | Ana

This returns Ana, a UK customer who has purchased and never been refunded, while excluding Ben, who is UK and has purchased but carries a refunded £75 order. The query reads almost like the English description: an ordinary WHERE for the country, a semi-join for “has purchased”, and an anti-join for “never refunded”. Stacking a semi-join and an anti-join on the same table is how most behavioural segments are actually built, and because each subquery selects only the single constant it needs and filters early, the whole thing scans little data, which is what BigQuery bills you for.

Work through these and you have the whole article in practice: the filter-not-merge mental model, semi-joins with EXISTSand IN, anti-joins with NOT EXISTS, the NOT IN null trap and its guard, the LEFT JOIN / IS NULL pattern, the “did X but not Y” self anti-join, filtered semi-joins, and stacked segments. The rule worth carrying out of all of it is short: use EXISTS and NOT EXISTS by default, treat NOT IN as a trap that fails silently on nulls, and remember that these are tools for filtering a list of rows, not for gluing two tables together.

See you soon.