Semi-Joins and Anti-Joins in BigQuery

Two of the most useful join patterns do not actually merge any data. A semi-join keeps the rows of one table that have a match in another, and an anti-join keeps the rows that have no match. They answer questions of membership, customers who have ordered, products never sold, sessions that never converted, and they do it without the duplication and clutter a regular join can bring. SQL does not have keywords literally called SEMI JOIN or ANTI JOIN. Instead you express them with EXISTS and IN for the semi-join, and NOT EXISTS and NOT IN for the anti-join. Understanding them as named concepts, rather than as one-off tricks, makes a whole class of filtering problems click.

The mental model: filter, do not merge

The key idea is that a semi-join or anti-join is a filter on the left table, using the right table only as a yes-or-no test. This is what sets them apart from an inner or left join. A regular join brings the other table’s columns into your result and can multiply your rows when a single left row matches many right rows, the fan-out problem. A semi-join does neither. It returns each qualifying left row exactly once and adds none of the right table’s columns. So when your real question is “which of these rows have a counterpart over there”, and you do not actually want the counterpart’s data, a semi-join or anti-join is the precise tool, and it sidesteps both duplication and unnecessary columns.

Semi-joins: keeping rows that match

A semi-join returns left-table rows that have at least one match on the right. The EXISTS form expresses this directly, with a correlated subquery that links the two tables on their key.

SELECT u.user_id, u.name
FROM `myproject.crm.users` AS u
WHERE EXISTS (
  SELECT 1 FROM `myproject.sales.orders` AS o WHERE o.user_id = u.user_id
)

This keeps only users who have placed at least one order, and each such user appears once no matter how many orders they have. The IN form expresses the same idea as membership in a list of keys, which many people find more readable for a simple single-key check.

SELECT u.user_id, u.name
FROM `myproject.crm.users` AS u
WHERE u.user_id IN (SELECT user_id FROM `myproject.sales.orders`)

Both are semi-joins. EXISTS frames it as “does a matching row exist”, and IN frames it as “is this value in that set”, and for a single key on a clean column they return the same result.

Anti-joins: keeping rows that do not match

An anti-join is the negation, keeping left rows with no match on the right. The NOT EXISTS form is the safe and readable default.

SELECT u.user_id, u.name
FROM `myproject.crm.users` AS u
WHERE NOT EXISTS (
  SELECT 1 FROM `myproject.sales.orders` AS o WHERE o.user_id = u.user_id
)

This returns users who have never ordered, the classic inactive-customer query. There is also a NOT IN form, but it comes with a serious caveat. If the subquery’s key column contains any null, NOT IN returns no rows at all, because a comparison against null evaluates to unknown rather than false, and that quietly poisons the whole result. NOT EXISTS does not have this problem, since it tests row by row for the presence of a match. For anti-joins, prefer NOT EXISTS, and treat NOT IN as safe only when you are certain the key column has no nulls.

Why not just use a regular join

You can fake an anti-join with a left join that keeps unmatched rows and then filters for the nulls, and you can fake a semi-join with an inner join followed by a deduplication. Both work, and both are worse. The left-join anti-join asks the reader to reason about why a null means “no match”, and the inner-join semi-join risks fan-out, returning a user once per order unless you remember to deduplicate. Semi-joins and anti-joins say what they mean, return each left row at most once, and carry no risk of accidental multiplication. Reserve inner and left joins for when you genuinely want to see the matched columns from both tables, and use the semi and anti patterns when you only want to filter by existence.

EXISTS or IN

When you do choose the EXISTS family over the IN family, a couple of guidelines help. On large datasets, EXISTS and NOT EXISTS tend to produce better query plans, because the engine can stop at the first match and optimise the correlation. On small or medium lists of values, IN reads simply and performs perfectly well. And on the question of nulls, the EXISTS family is the safer choice, since it avoids the NOT IN trap entirely. BigQuery generally optimises EXISTS well, so when in doubt on a large table, reach for it.

A behavioral segmentation example

Anti-joins become genuinely powerful when the right table is the same as the left, which lets you express behavioral segments. Suppose you want customers who bought product A but never bought product B. You filter the orders to product A, then anti-join against the same orders table filtered to product B for the same user.

SELECT DISTINCT o.user_id
FROM `myproject.sales.orders` AS o
WHERE o.product = 'A'
  AND NOT EXISTS (
    SELECT 1 FROM `myproject.sales.orders` AS ob
    WHERE ob.user_id = o.user_id
      AND ob.product = 'B'
  )

This finds exactly the segment of buyers who took one action but not the other, the kind of definition that drives targeting and retention work. Expressing it as a self anti-join is far cleaner than trying to assemble it from joins and group filters, and the same shape generalises to any “did X but not Y” question.

Keeping them fast

A few habits keep semi-joins and anti-joins efficient. Filter inside the subquery as early as possible, as the product example does, so the existence test runs against fewer rows. Match on numeric or integer keys where you can, since they compare faster than strings. Select only the columns you need, because BigQuery still bills by data scanned. And on large tables, partitioning and clustering on the join key gives the optimiser real leverage. Combine those with the right pattern, EXISTS and IN for presence, NOT EXISTS for absence and its null-safety, and these existence filters become some of the most efficient and expressive tools in your BigQuery toolkit.

See you soon.