EXISTS and NOT EXISTS in BigQuery: Testing for Related Records

A surprising amount of analysis comes down to a yes-or-no question about another table. Has this user ever ordered? Does this order have a shipment? Did this visitor view a product but never buy? EXISTS and NOT EXISTS are the cleanest tools for exactly these questions. They are filters that check whether related rows exist somewhere else, without dragging that other table’s data into your result. Once you see how they work, they often replace clumsier joins and become your default for presence and absence checks.

How EXISTS thinks

EXISTS takes a subquery and returns true the moment that subquery finds at least one matching row. It does not care what the subquery returns, only whether it returns anything, which is why the convention is to write SELECT 1 inside it. The subquery is correlated, meaning it refers back to the outer query, so it runs once per outer row, asking “for this particular row, does a match exist over there?”

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 every user for whom at least one order exists. The link between the tables lives in the WHERE clause inside the subquery, where the order’s user id is matched to the outer user’s id. Because EXISTS stops as soon as it finds one match, it does not matter whether the user has one order or a thousand, the row is kept exactly once. That single-keep behaviour is one of its quiet advantages over a join.

NOT EXISTS, the absence test

NOT EXISTS is the mirror image. It returns true when the subquery finds nothing, so it keeps the outer rows that have no match at all. This is the natural way to express “never”, as in users who have never placed an order.

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
)

The structure is identical to the EXISTS version, with only the negation changing the meaning from “has at least one” to “has none”. This is what databases call an anti-join, finding the rows in one set that are absent from another.

Where these earn their keep

The pattern shines on real operational questions. To find orders that have not yet shipped, you look for orders with no matching shipment.

SELECT o.order_id, o.user_id
FROM `myproject.sales.orders` AS o
WHERE NOT EXISTS (
  SELECT 1
  FROM `myproject.logistics.shipments` AS s
  WHERE s.order_id = o.order_id
)

To find shoppers who looked but never bought, a classic conversion-analysis question, you take product viewers and exclude anyone with a purchase.

SELECT DISTINCT v.user_id
FROM `myproject.web.product_views` AS v
WHERE NOT EXISTS (
  SELECT 1
  FROM `myproject.sales.purchases` AS p
  WHERE p.user_id = v.user_id
)

Both read almost like the English question they answer, which is part of why these filters are so pleasant to work with.

Choosing between EXISTS, JOIN, and IN

These are not the only ways to relate tables, and knowing when to reach for each matters. A join is the right choice when you actually want data from the other table, not just to know it exists, but it carries the risk of duplicating rows when the relationship is not one to one. IN works for checking a value against a simple list and reads naturally for small sets, but it can slow down on large lists of values. EXISTS is purpose-built for testing existence, and because it can stop at the first match, it tends to be efficient even when the related table is huge. NOT EXISTS is the efficient, clean way to express the anti-join that the other methods handle awkwardly. The short version is that if you want the related data, join; if you only want to know whether it exists, use EXISTS or NOT EXISTS.

NOT EXISTS versus the left join trick

There is a well-known alternative for the anti-join, a left join that keeps unmatched rows followed by a filter for the nulls those non-matches produce. It works, but it is wordier and asks the reader to follow a two-step argument.

-- The left join approach
SELECT u.user_id
FROM `myproject.crm.users` AS u
LEFT JOIN `myproject.sales.orders` AS o ON u.user_id = o.user_id
WHERE o.user_id IS NULL
-- The NOT EXISTS approach, cleaner and usually faster
SELECT u.user_id
FROM `myproject.crm.users` AS u
WHERE NOT EXISTS (
  SELECT 1 FROM `myproject.sales.orders` AS o WHERE o.user_id = u.user_id
)

Both return users with no orders, but the NOT EXISTS version says what it means directly, where the left join version says it by side effect, finding the rows where the join failed. For readability and often for performance, NOT EXISTS is the better default.

The null trap worth knowing

The one place anti-joins bite people is nulls, and it is worth understanding because it is a real correctness issue rather than a style preference. If you were tempted to write this as NOT IN against a subquery of order user ids, and any of those ids were null, the NOT IN would return no rows at all, because a comparison against null is unknown rather than false. NOT EXISTS does not suffer from this, since it asks only whether a matching row exists and handles the comparison row by row. That null-safety is a strong reason to prefer NOT EXISTS over NOT IN for anti-joins. When NOT EXISTS gives a result that surprises you, nulls in the join columns are still the first thing to inspect, but you avoid the silent total-wipeout that NOT IN can cause.

Habits that keep these clean

A few principles make EXISTS and NOT EXISTS reliable. Use them for presence checks rather than for pulling data, since their whole point is to test, not to retrieve, and reach for a join when you genuinely need the other table’s columns. Prefer NOT EXISTS over the left-join-and-filter trick for anti-joins, both for clarity and for its null-safety over NOT IN. Keep the subquery simple, matching on keys and avoiding heavy calculations inside it, so the optimiser can do its best work. And test on a small slice of data first, because anti-join logic is easy to get subtly backwards, and a quick sanity check catches it before it scales. Used this way, EXISTS and NOT EXISTS turn a whole class of relationship questions into short, readable, efficient filters.

See you soon.