FULL JOIN: Preserve Unmatched Rows on Both Sides

FULL JOIN preserves unmatched rows from both inputs while returning the row pairs that satisfy its predicate. I retain each side’s identifier when reviewing the result. A missing joined column can mean an absent partner or missing source data.

Keep matched and unmatched source identities visible

The left input contains keys one, two and NULL. The right input contains keys two, three and NULL. Each row also has a separate nonmissing source identifier.

The equality predicate matches key two from the left with key two from the right. That expected output row contains both source identifiers. Its MatchStatus is Matched.

Key one has no right partner and remains as a left-only row. Key three has no left partner and remains as a right-only row. The absent partner’s columns are filled with NULL.

I keep LeftId and RightId separate rather than immediately combine them into one display key. Those identifiers reveal which source supplied each result row. They also support a reliable match-status expression.

WITH LeftRows AS
(
    SELECT LeftId,MatchKey FROM (VALUES (1,CAST(1 AS int)),(2,2),(3,CAST(NULL AS int))) AS v(LeftId,MatchKey)
), RightRows AS
(
    SELECT RightId,MatchKey FROM (VALUES (11,CAST(2 AS int)),(12,3),(13,CAST(NULL AS int))) AS v(RightId,MatchKey)
)
SELECT l.LeftId,l.MatchKey AS LeftKey,r.RightId,r.MatchKey AS RightKey,
    CASE WHEN l.LeftId IS NULL THEN 'Right only'
         WHEN r.RightId IS NULL THEN 'Left only' ELSE 'Matched' END AS MatchStatus
FROM LeftRows AS l FULL OUTER JOIN RightRows AS r ON l.MatchKey=r.MatchKey
ORDER BY CASE WHEN l.LeftId IS NULL THEN 1 ELSE 0 END,l.LeftId,r.RightId;

Ordinary equality does not join two missing keys

The two rows with missing MatchKey values do not satisfy the ordinary equality predicate. Their comparison is Unknown. They therefore remain as two separate unmatched rows.

The expected result contains five rows altogether. One is matched, two are left-only and two are right-only. Both missing-key source rows are preserved without being paired together.

I classify source presence using the nonmissing identifiers rather than MatchKey. A source row can exist even when its matching key is NULL. Testing the key alone would confuse those two conditions.

This example intentionally keeps missing-key equality separate from the outer-join preservation rule. A different null-matching predicate would change which pairs form. That would be a new matching contract.

Preserving both sides does not guarantee one row per key

The present nonmissing keys are unique within each input. That keeps the matching pair easy to enumerate. The example does not assume every real input has the same uniqueness.

If several rows share a matching key on both sides, multiple matching pairs can result. An outer join preserves those pairs as well as unmatched rows. It does not silently choose one preferred partner.

Before adapting the query, state the expected grain of each input. A reconciliation between unique keys differs from a reconciliation between repeated events. The predicate and output model must reflect that difference.

I would keep source identifiers in the review projection until the matching relationship is confirmed. Distinct visible labels can hide duplicate pairs. Removing those identifiers too early makes multiplicity harder to explain.

Review later filters against the preservation requirement

The ON predicate determines which source rows match. A later WHERE predicate determines which joined rows remain in the final result. A condition requiring a right-side value can remove left-only rows.

If unmatched rows are required for reconciliation, review that later filter explicitly. An apparently harmless condition can contradict the preservation goal. Keep the unmatched source identifiers visible in the resulting report.

The final ORDER BY places present left identifiers first, followed by right-only rows. This is a display contract only. It does not influence which pairs satisfy the join.

Read the matched pair beside both sides’ unmatched rows. The missing-key source identifiers show that those rows still exist. Their NULL keys simply do not satisfy the ordinary equality predicate.

When testing reconciliation, include an unmatched key on each side and a missing key with a known source identifier. Verify all expected source identities survive. That checks preservation without inventing an equality relationship for missing data.

Keep both identifiers in your review query until the matches are confirmed.

A missing partner is not a missing key, it is a source row without a match.

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