Sort/Merge Optimization for Outer Joins

Go Up to Using Outer Joins

The Sort/Merge option for the outer joins algorithm recognizes outer and inner streams of an outer join and matches an outer row with a null-valued inner row when there is no matching row in the inner stream.

For full outer joins, the outer and inner streams are swapped after producing matching and null-matched rows for the first stream. The first stream becomes the inner stream and what was the second stream becomes the outer stream. These rows are then left outer joined and only those rows in which the outer stream is matched with nulls are produced. The matching rows on the join terms are filtered out because they were produced before the two streams were swapped during the first pass.