What is Full Outer Join With Exclusion? – Interview Question of the Week #286

Question: What is a full outer join with exclusion? It is a FULL OUTER JOIN followed by a filter that keeps only unmatched rows from either side. “With exclusion” describes that query pattern; it isn’t a separate T-SQL JOIN keyword.

A regular client asked why my introduction to JOINs didn’t mention this name. I had illustrated the same pattern under “NOT INNER JOIN.” That informal label was intended to describe the rows outside the match, not an actual SQL syntax.

Keep the unmatched rows on both sides

DECLARE @LeftTable table (ID int NOT NULL PRIMARY KEY, Label varchar(10));
DECLARE @RightTable table (ID int NOT NULL PRIMARY KEY, Label varchar(10));
INSERT @LeftTable VALUES (1,'LeftOnly'), (2,'Shared');
INSERT @RightTable VALUES (2,'Shared'), (3,'RightOnly');

SELECT l.ID AS LeftID, l.Label AS LeftLabel,
       r.ID AS RightID, r.Label AS RightLabel
FROM @LeftTable AS l
FULL OUTER JOIN @RightTable AS r ON r.ID = l.ID
WHERE l.ID IS NULL OR r.ID IS NULL
ORDER BY COALESCE(l.ID, r.ID);

The output contains (1, LeftOnly, NULL, NULL) and (NULL, NULL, 3, RightOnly). ID 2 is present on both sides and is excluded. A FULL OUTER JOIN by itself would also include its matched row.

Choose a reliable unmatched-row test

In this example ID is NOT NULL in both source tables. Its NULL in the joined result therefore means that side is absent. Don’t test an arbitrary nullable description column; a real matched row may have a NULL description. With nullable join keys or more complex join predicates, use a source row identifier that cannot be NULL to test whether a row exists.

Two NULL keys do not match under ordinary equality. Such source rows are unmatched, rather than becoming a shared row. The join predicate defines what “common” means.

The Venn picture is an introduction, not a complete model of SQL’s duplicate-row behavior. This pattern preserves unmatched source rows, including duplicates where the table permits them. EXCEPT-based solutions remove duplicates and have different NULL semantics, so they aren’t automatic drop-in replacements.

The client’s question came from my Comprehensive Database Performance Health Check work. The original consulting series starts here; its companion posts cover engagement promises, four-hour optimization, sharing scripts, avoiding repeat problems and getting started.

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