EXISTS and NOT EXISTS in BigQuery: 10 Code-Along Examples

The EXISTS and NOT EXISTS article draws a line that changes how you write SQL: these operators test for the presence of related records, they do not retrieve them. EXISTS asks “is there at least one matching row?” and answers true or false, which means the outer row is kept exactly once no matter how many matches exist, and no column from the subquery ever reaches your output. This workbook builds that skill: the correlation that makes it work, the SELECT 1 convention, multiple conditions, EXISTS as a flag rather than a filter, nested checks across three tables, and the null behaviour that makes NOT EXISTS the safe choice its alternative is not.

Run the setup once, then every example runs in the BigQuery console. If you have already worked through the semi-joins and anti-joins code-along, this one goes deeper on the operator itself.

Setup: run this once

CREATE SCHEMA IF NOT EXISTS scratch;
-- five users; Eve has never ordered anything
CREATE OR REPLACE TABLE scratch.users AS
SELECT * FROM UNNEST([
  STRUCT(101 AS user_id, 'Ana'  AS name, 'UK' AS country),
  (102, 'Ben',  'UK'),
  (103, 'Cara', 'DE'),
  (104, 'Dan',  'US'),
  (105, 'Eve',  'US')
]);
-- orders; note order 6 is a guest checkout with a NULL user_id
CREATE OR REPLACE TABLE scratch.orders AS
SELECT * FROM UNNEST([
  STRUCT(1 AS order_id, 101 AS user_id, 40 AS amount, 'complete' AS status),
  (2, 101, 55,  'complete'),
  (3, 102, 120, 'complete'),
  (4, 103, 20,  'cancelled'),
  (5, 104, 200, 'complete'),
  (6, CAST(NULL AS INT64), 50, 'complete')
]);
-- only orders 1 and 3 have actually shipped
CREATE OR REPLACE TABLE scratch.shipments AS
SELECT * FROM UNNEST([
  STRUCT(1 AS shipment_id, 1 AS order_id, DATE '2026-01-05' AS shipped_date),
  (2, 3, DATE '2026-01-08')
]);
CREATE OR REPLACE TABLE scratch.product_views AS
SELECT * FROM UNNEST([
  STRUCT(1 AS view_id, 101 AS user_id, 'Books' AS product),
  (2, 102, 'Games'),
  (3, 105, 'Games'),
  (4, 103, 'Music')
]);
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 101 AS user_id, 'Books' AS product),
  (2, 102, 'Games')
]);

Five users, six orders including one guest checkout with no user_id, two shipments, and separate view and purchase logs. That null in orders sits quietly until Example 10, where it breaks something.

1. EXISTS: does a related record exist?

EXISTS wraps a subquery and evaluates to true if that subquery returns any row at all. Used in a WHERE clause it keeps the outer rows for which a match exists, each one exactly once.

SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (
  SELECT 1
  FROM scratch.orders AS o
  WHERE o.user_id = u.user_id
)
ORDER BY u.user_id;

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

Four users kept, Eve dropped because nothing in orders carries her id. The detail worth pausing on is Ana: she has two orders, and she appears once. EXISTS is a yes-or-no test, so once the subquery has found a single matching row the answer is settled and the outer row is kept, which means no DISTINCT is needed and no row can multiply. That single-keep behaviour is the operator’s defining property and the reason it exists alongside joins.

2. The correlation is the whole point

The line o.user_id = u.user_id is what ties the subquery to the current outer row. Remove it and EXISTS stops asking about the user and starts asking whether the table has any rows at all.

-- WRONG: no correlation, so the subquery ignores the outer row entirely
SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (
  SELECT 1
  FROM scratch.orders AS o     -- no reference to u
)
ORDER BY u.user_id;

user_id | name
--------+------
101     | Ana
102     | Ben
103     | Cara
104     | Dan
105     | Eve

Eve is back, and so is everyone else, because the subquery now returns rows regardless of which user is being tested: orders is not empty, so EXISTS is true for every single outer row. The subquery ran five times and gave the same answer five times. This is the most common EXISTS bug, and it is silent, since the query succeeds and simply returns too much. The rule to internalise is that a subquery inside EXISTS must reference the outer table, and if it does not, you have written an expensive way of asking whether a table has any rows.

3. The SELECT 1 convention

Because EXISTS only cares whether a row came back, the subquery’s select list is never used. SELECT 1 is the convention that says so out loud, and proving the alternatives are identical is worth doing once.

SELECT
  u.user_id,
  u.name,
  EXISTS (SELECT 1     FROM scratch.orders o WHERE o.user_id = u.user_id) AS with_1,
  EXISTS (SELECT *     FROM scratch.orders o WHERE o.user_id = u.user_id) AS with_star,
  EXISTS (SELECT "abc" FROM scratch.orders o WHERE o.user_id = u.user_id) AS with_text
FROM scratch.users AS u
ORDER BY u.user_id;

user_id | name | with_1 | with_star | with_text
--------+------+--------+-----------+----------
101     | Ana  | true   | true      | true
102     | Ben  | true   | true      | true
103     | Cara | true   | true      | true
104     | Dan  | true   | true      | true
105     | Eve  | false  | false     | false

Three different select lists, three identical columns. The engine discards whatever you project and reports only whether a row existed, so SELECT * costs nothing extra and SELECT "abc" is not clever. SELECT 1 wins on readability alone: it signals to the next person that the column list is deliberately meaningless and the point is presence. Notice too that EXISTS has produced a boolean column here rather than filtering, which Example 6 develops.

4. NOT EXISTS: the absence test

Negate it and the operator answers the opposite question. NOT EXISTS keeps outer rows for which the subquery finds nothing, which is how you locate records with no related counterpart.

SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM scratch.orders AS o
  WHERE o.user_id = u.user_id
)
ORDER BY u.user_id;

user_id | name
--------+------
105     | Eve

Eve alone, the one user with no order to her name. The questions this pattern answers are some of the most commercially useful in any database: customers who never bought, products never sold, accounts with no login in ninety days, invoices with no payment. Each is the same shape, a table of things and a table of events, with NOT EXISTS finding the things that never appear in the events. It is also, as Example 10 shows, the only formulation of this question that behaves correctly when nulls are present.

5. Several conditions inside the subquery

The subquery is ordinary SQL, so extra conditions narrow what counts as a match. Adding them changes the meaning of “related record” precisely.

SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (
  SELECT 1
  FROM scratch.orders AS o
  WHERE o.user_id = u.user_id
    AND o.status = 'complete'     -- not cancelled
    AND o.amount > 100            -- and a big one
)
ORDER BY u.user_id;

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

Ben qualifies on his completed £120 order and Dan on his £200 one. Ana disappears despite having two completed orders, because neither exceeds £100, and Cara disappears because her only order was cancelled, so it is not a match however large it might have been. Every condition you add tightens the definition of the relationship being tested, and putting them inside the subquery rather than outside is also the performance advice from the article: filter early, so the engine has less to scan before it can answer the presence question.

6. EXISTS as a flag, not a filter

EXISTS returns a boolean, so it can sit in the SELECT list to label rows rather than remove them. This keeps everyone in the result while marking who has what.

SELECT
  u.user_id,
  u.name,
  EXISTS (SELECT 1 FROM scratch.orders o
          WHERE o.user_id = u.user_id) AS has_ordered,
  EXISTS (SELECT 1 FROM scratch.purchases p
          WHERE p.user_id = u.user_id) AS has_purchased,
  CASE
    WHEN EXISTS (SELECT 1 FROM scratch.purchases p WHERE p.user_id = u.user_id)
      THEN 'customer'
    WHEN EXISTS (SELECT 1 FROM scratch.product_views v WHERE v.user_id = u.user_id)
      THEN 'browser'
    ELSE 'dormant'
  END AS segment
FROM scratch.users AS u
ORDER BY u.user_id;

user_id | name | has_ordered | has_purchased | segment
--------+------+-------------+---------------+----------
101     | Ana  | true        | true          | customer
102     | Ben  | true        | true          | customer
103     | Cara | true        | false         | browser
104     | Dan  | true        | false         | dormant
105     | Eve  | false       | false         | browser

Nobody is filtered out, and every user carries two flags plus a segment label. The CASE chain is the useful pattern here: conditions are tested top to bottom and the first true one wins, so a user who purchased is labelled a customer even though they also browsed, and Dan lands on “dormant” because he ordered but neither purchased nor viewed in these particular logs. Building segments this way keeps the full population visible, which is what a dashboard needs, where the WHEREclause version would silently hide the rows you did not match.

7. Combining EXISTS and NOT EXISTS

Stack the two operators and you carve out behavioural segments: did one thing, but not another. Here, users who looked at a product and never bought anything.

SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (                       -- viewed something
        SELECT 1 FROM scratch.product_views AS v
        WHERE v.user_id = u.user_id
      )
  AND NOT EXISTS (                   -- but never purchased
        SELECT 1 FROM scratch.purchases AS p
        WHERE p.user_id = u.user_id
      )
ORDER BY u.user_id;

user_id | name
--------+------
103     | Cara
105     | Eve

Cara viewed Music and Eve viewed Games, and neither appears in the purchases log, so both fall into the segment. Ana and Ben are excluded by the NOT EXISTS despite passing the first test, since they did buy. This “did X but not Y” shape is the backbone of practical analytics, abandoned browsers, trial users who never converted, customers who bought one product but not its companion, and stacking the two operators reads almost exactly like the English sentence describing the segment.

8. Nested EXISTS across three tables

The subquery inside EXISTS can itself contain EXISTS, which is how you follow a chain of relationships. This finds users who have at least one order that has not shipped.

SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (
  SELECT 1
  FROM scratch.orders AS o
  WHERE o.user_id = u.user_id           -- the user's orders...
    AND NOT EXISTS (
      SELECT 1
      FROM scratch.shipments AS s
      WHERE s.order_id = o.order_id     -- ...that have no shipment
    )
)
ORDER BY u.user_id;

user_id | name
--------+------
101     | Ana
103     | Cara
104     | Dan

Read it inside out. The inner NOT EXISTS asks, for one order, whether a shipment exists, and orders 2, 4, and 5 fail that test. The outer EXISTS then asks, for one user, whether any of their orders failed it, which is true for Ana (order 2), Cara (order 4), and Dan (order 5). Ben is excluded because his only order, number 3, did ship. Note the two levels of correlation, the outer subquery referencing u and the inner one referencing o, which is what walks the chain from user to order to shipment without a single join.

9. EXISTS versus JOIN: presence or data?

The decision rule is short. If you need columns from the other table, join. If you only need to know whether a related row exists, use EXISTS, and avoid the fan-out a join produces.

-- JOIN: retrieves data, and multiplies rows per match
SELECT u.user_id, u.name, o.order_id, o.amount
FROM scratch.users AS u
JOIN scratch.orders AS o ON o.user_id = u.user_id
ORDER BY u.user_id, o.order_id;

user_id | name | order_id | amount
--------+------+----------+-------
101     | Ana  | 1        | 40
101     | Ana  | 2        | 55
102     | Ben  | 3        | 120
103     | Cara | 4        | 20
104     | Dan  | 5        | 200

-- EXISTS: tests presence, one row per user, no order columns available
SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE EXISTS (SELECT 1 FROM scratch.orders AS o WHERE o.user_id = u.user_id)
ORDER BY u.user_id;

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

Five rows against four, and the difference is Ana appearing twice in the join because she has two orders. That duplication is correct when you want the order detail and a bug when you wanted a user list, which is why reaching for a join plus DISTINCT to answer a presence question is a habit worth breaking. The trade is explicit: the join gives you order_id and amount at the cost of fan-out, and EXISTS gives you a clean one-row-per-user answer at the cost of having no order columns to show.

10. Null safety, and why NOT EXISTS wins

The obvious alternative to NOT EXISTS is NOT IN, and it fails silently when the subquery’s column contains a null. The guest checkout in the seed data is that null.

-- NOT IN: looks correct, returns NOTHING
SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE u.user_id NOT IN (SELECT user_id FROM scratch.orders)
ORDER BY u.user_id;

(0 rows)

-- NOT EXISTS: correct, no guard needed
SELECT u.user_id, u.name
FROM scratch.users AS u
WHERE NOT EXISTS (
  SELECT 1 FROM scratch.orders AS o WHERE o.user_id = u.user_id
)
ORDER BY u.user_id;

user_id | name
--------+------
105     | Eve

Same intent, opposite outcomes. NOT IN expands to “not equal to every value in the list”, and comparing Eve’s id against the null from order 6 yields unknown rather than false, so the engine can never confirm she is absent and rejects every row. No error is raised, which is precisely what makes it dangerous: the query looks fine and quietly returns an empty result that a dashboard will report as zero. NOT EXISTS never performs that comparison, so the null is simply a row that fails to match and the answer comes out right. Adding WHERE user_id IS NOT NULL inside the subquery would rescue NOT IN, but you would have to remember it every single time, forever, which is a poor trade against an operator that is safe by default.

Work through these and you have the whole article in practice: EXISTS as a presence test that keeps each row once, the correlation that makes it meaningful, the SELECT 1 convention, NOT EXISTS for absence, narrowed subqueries, EXISTS as a boolean flag and segment builder, stacked and nested checks across three tables, the presence-versus-data choice against joins, and the null behaviour that settles the NOT EXISTS versus NOT IN argument for good. The habit to leave with is the article’s own: use these operators to test for related records rather than to pull them, keep the subquery simple and correlated, and default to NOT EXISTS whenever you are asking what is missing.

See you soon.