Avoid OR in JOIN Clauses

When joining datasets on multiple possible keys, you might be tempted to use OR in your JOIN condition. This pattern prevents the query planner from optimizing the join, often causing queries to run for hours instead of seconds.

Using OR in JOIN conditions can make queries 100x slower or more. Always restructure OR conditions into single-key joins using the techniques in this guide.

The anti-pattern

This query attempts to match records where either user_id or email matches:

SELECT p.purchase_id, p.amount, u.name, u.email
FROM Purchases p
JOIN Users u
  ON p.user_id = u.id
  OR p.email = u.email;

While logically correct, this pattern prevents the query planner from using efficient join algorithms. The planner cannot build a hash table or use indexes effectively when it must evaluate two conditions with OR.

Solution 1: Array flattening with UNNEST

The recommended approach is to combine your join keys into an array, flatten it with UNNEST, and then join on a single key column.

How it works

Combine keys into an array on each side of the join
UNNEST the arrays to create one row per potential key
JOIN on the single key column using standard equality

Example

WITH Purchases_flat AS (
    SELECT
      p.*,
      key
    FROM Purchases p
    CROSS JOIN UNNEST([p.user_id, p.email]) AS t(key)
),
Users_flat AS (
    SELECT
      u.*,
      key
    FROM Users u
    CROSS JOIN UNNEST([u.id, u.email]) AS t(key)
)
SELECT DISTINCT
  pf.purchase_id,
  pf.item,
  pf.amount,
  uf.user_name,
  uf.email
FROM Purchases_flat pf
JOIN Users_flat uf
  ON pf.key = uf.key;

This query:

Creates arrays containing both possible join keys
Uses UNNEST to expand each row into multiple rows (one per key)
Joins on a single key column, which the planner can optimize efficiently
Uses SELECT DISTINCT to remove duplicate matches

The UNNEST approach works well when you have more than two possible join keys or when the keys are already stored as arrays.

Solution 2: Using UNION

For simple cases with exactly two join conditions, UNION provides a cleaner alternative:

SELECT
  p.purchase_id,
  p.item,
  p.amount,
  u.user_name,
  u.email
FROM Purchases p
JOIN Users u ON p.user_id = u.id

UNION

SELECT
  p.purchase_id,
  p.item,
  p.amount,
  u.user_name,
  u.email
FROM Purchases p
JOIN Users u ON p.email = u.email;

This approach:

Runs two optimized single-key joins separately
Combines the results and automatically removes duplicates with UNION
May be easier to read for simple two-condition cases

Use UNION ALL instead of UNION if you want to keep duplicates, but be aware this may produce unexpected results when both conditions match the same record.

Choosing the right approach

Approach	Best for	Trade-offs
UNNEST	Multiple keys, complex queries, keys already in arrays	Requires explicit DISTINCT, slightly more complex syntax
UNION	Exactly two conditions, readability is priority	Repeats query logic, harder to maintain with many conditions

Both approaches transform the problematic OR condition into single-key joins that the query planner can optimize using hash joins or indexes.

To understand why OR conditions cause these performance problems, see Understanding JOIN Performance.

Understanding JOIN Performance

Learn why OR in JOINs prevents optimization

Query Optimization

More techniques for writing efficient queries

Joining Datasets

Fundamentals of combining data from multiple datasets

NQL Functions

Reference for UNNEST and other NQL functions

Query Optimization NQL Troubleshooting

⌘I

Overview

Platform

Data Ingestion

Data Planes

Querying with NQL

Data Collaboration

Identity Resolution

Data Activation

Audience Studio

Connectors

Compliance

Rosetta Stone

Account Settings

Workflows

Webhooks

Data Collaboration MCP Server

Tools

SDKs

The anti-pattern

Solution 1: Array flattening with UNNEST

How it works

Example

Solution 2: Using UNION

Choosing the right approach

Understanding JOIN Performance

Query Optimization

Joining Datasets

NQL Functions

​The anti-pattern

​Solution 1: Array flattening with UNNEST

​How it works

​Example

​Solution 2: Using UNION

​Choosing the right approach

​Related content

Understanding JOIN Performance

Query Optimization

Joining Datasets

NQL Functions

The anti-pattern

Solution 1: Array flattening with UNNEST

How it works

Example

Solution 2: Using UNION

Choosing the right approach

Related content