The Semi-Join Advantage

BigQuery handles "EXISTS" and "IN" differently under the hood. "EXISTS" performs a semi-join with short-circuit evaluation, meaning it probes the right-hand side until the first match and stops immediately. In contrast, "IN" typically materializes the entire subquery result, building and deduplicating a complete key set before filtering the left table. On large tables, this materialization triggers extra scans and shuffles that directly inflate both query latency and bytes processed. The performance gap widens dramatically when the right-hand side contains millions of rows or exhibits high cardinality.

Pattern Comparison

Preferred Pattern (EXISTS):

SELECT e.*
FROM events e
WHERE EXISTS (
  SELECT 1
  FROM purchases p
  WHERE p.user_id = e.user_id
    AND p.event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
                         AND CURRENT_DATE()
);

Avoid (IN with large subquery):

SELECT e.*
FROM events e
WHERE e.user_id IN (
  SELECT DISTINCT user_id 
  FROM purchases
);

The "EXISTS" pattern composes cleanly with partition pruning via date filters and clustering pruning via correlated keys like "user_id", cutting both scan volume and shuffle overhead.

NULL Semantics and Safety

"EXISTS" gracefully ignores duplicates and handles NULLs without surprises. "NOT IN" becomes a trap when the subquery can return NULL values, as the comparison produces UNKNOWN and silently filters out all rows. This behavior has burned countless production queries. The safe approach is always "WHERE NOT EXISTS" for negation filters, which sidesteps NULL complications entirely and maintains predictable behavior across all data distributions.

When to Use EXISTS and When IN Still Works

Replace "JOIN … DISTINCT" or "COUNT(*) > 0" patterns with "EXISTS" to express presence checks without materialization overhead. These anti-patterns force BigQuery to materialize all matches before deduplication, creating unnecessary memory pressure and shuffle operations. "EXISTS" short-circuits on the first match, making it ideal for large fact tables joined to event logs or transaction histories.

Reserve "IN" for tiny literal lists like "IN ('US', 'CA', 'GB')" or small dimension tables under a few hundred rows already cached in memory. The crossover point typically sits around 1000 rows, but watch your bytes processed metrics. Always use "SELECT 1" inside "EXISTS" subqueries rather than selecting columns, and include explicit partition predicates on the right-hand side to unlock pruning that cuts scanned data by orders of magnitude.