OR in a JOIN: Preserve Matching Pairs When Rewriting

OR in a JOIN can match either key without returning a matching row pair twice. A rewrite must preserve that rule. Test duplicate pairs and NULL inputs before investigating its execution plan.

Build cases that expose the rule

Run these four blocks in one isolated query window, in order. They use session-local temporary tables. The inputs include both-key matches, one-key matches, missing keys and distinct rows with identical key values.

CREATE TABLE #JoinA(Id int PRIMARY KEY,X int NULL,Y int NULL);
CREATE TABLE #JoinB(Id int PRIMARY KEY,X int NULL,Y int NULL);
INSERT #JoinA VALUES (1,10,20),(2,30,40),(3,NULL,50),
                     (4,60,NULL),(5,NULL,NULL);
INSERT #JoinB VALUES (1,10,20),(2,10,99),(3,88,40),
                     (4,NULL,50),(5,60,NULL),(6,999,999),(7,10,20);
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.X=b.X OR a.Y=b.Y
ORDER BY AId,BId;

The expected pairs are (1,1), (1,2), (1,7), (2,3), (3,4) and (4,5). Pair (1,1) matches both conditions but appears once. Row 7 has the same key values as row 1, but remains a different target row.

Keep pair identity when using UNION

SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.X=b.X
UNION
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.Y=b.Y
ORDER BY AId,BId;

UNION removes duplicate projected rows. Including both primary-key identifiers makes the projection identify the original row pair. Projecting only equal-looking payload values can collapse legitimate pairs.

Plain UNION ALL returns a pair twice when both branches match it. In this input, pairs (1,1) and (1,7) expose that mistake. Faster execution would not make the extra rows correct.

Make the second branch disjoint, including NULL

SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.X=b.X
UNION ALL
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.Y=b.Y
AND (a.X<>b.X OR a.X IS NULL OR b.X IS NULL)
ORDER BY AId,BId;

The first branch accepts X matches. The second accepts Y matches only when X did not match. A simple X inequality loses valid Y matches when either X value is NULL.

The added NULL tests retain those cases. Two NULL keys do not equal each other under normal SQL equality. The rewrite still emits each matching row pair once.

Compare multiplicity in both directions

This comparison groups each pair with its count before applying EXCEPT. Both differences should be empty. Plain set comparison without counts can conceal repeated copies of the same pair.

;WITH OriginalPairs AS (
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.X=b.X OR a.Y=b.Y
), RewrittenPairs AS (
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.X=b.X
UNION ALL
SELECT a.Id AS AId,b.Id AS BId FROM #JoinA AS a
JOIN #JoinB AS b ON a.Y=b.Y
AND (a.X<>b.X OR a.X IS NULL OR b.X IS NULL)
), OriginalCounts AS (
 SELECT AId,BId,COUNT_BIG(*) AS Copies
 FROM OriginalPairs GROUP BY AId,BId
), RewrittenCounts AS (
 SELECT AId,BId,COUNT_BIG(*) AS Copies
 FROM RewrittenPairs GROUP BY AId,BId
)
SELECT N'Original minus rewrite' AS Difference,AId,BId,Copies
FROM (SELECT * FROM OriginalCounts
      EXCEPT SELECT * FROM RewrittenCounts) AS Missing
UNION ALL
SELECT N'Rewrite minus original',AId,BId,Copies
FROM (SELECT * FROM RewrittenCounts
      EXCEPT SELECT * FROM OriginalCounts) AS Extra;
DROP TABLE #JoinA;
DROP TABLE #JoinB;

The final statements remove the two temporary tables. Preserve both-match, NULL and equal-payload cases when changing the projection or relationship rule. Unique pair identifiers are part of this example’s equivalence argument.

Measure performance after proving the result

No speedup is claimed from this tiny input. On representative data, compare actual plans, logical reads and elapsed time under the same conditions. Include the cost of duplicate removal and any additional indexes.

The optimizer chooses physical operators from the schema, statistics and data distribution. Splitting the predicate does not guarantee seeks or a faster plan. Apply this rewrite to inner joins deliberately; outer joins need their own unmatched-row analysis.

Run the pairs on your own tables before you trust any speedup.

A faster rewrite is not a correct rewrite, it is a candidate until the pairs match.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.