Full Outer Joins

Right-Only Rows Get Left NULLs

Unmatched right rows are preserved with left NULLs.

highlighted = computed this step

Right-only rows

If a right row has no matching left row, the output keeps it and pads left columns with NULL.

right only plus left NULLs\text{right only plus left NULLs}

Read right-only rows

There are 2 right-only output rows.

right only=2\text{right only}=2

Full-outer-join examples are tiny finite-table transforms; SQL dialect completeness, type coercion, optimizer behavior, execution cost, indexing, output ordering guarantees, and database-product claims are out of scope.

Right-only rows get left NULLs: left tableemp_idnamedept_id1Ada102Ben203Cy304DeeNULL Right-only rows get left NULLs: right tabledept_iddept_name10ops20eng20platform40legalNULLunknown candidate key matchesleftSourcerightSourceleftKeyrightKeymatched001010yes011020no021020no031040no0410NULLno102010no112020yes122020yes132040no1420NULLno203010no213020no223020no233040no2430NULLno30NULL10no31NULL20no32NULL20no33NULL40no34NULLNULLno FULL OUTER JOIN output rowsleft.emp_idleft.nameleft.dept_idright.dept_idright.dept_name1Ada1010ops2Ben2020eng2Ben2020platform3Cy30NULLNULL4DeeNULLNULLNULLNULLNULLNULL40legalNULLNULLNULLNULLunknown output row classificationclassleftSourcerightSourcerowmatched00[1, Ada, 10, 10, ops]matched11[2, Ben, 20, 20, eng]matched12[2, Ben, 20, 20, platform]left_only2NULL[3, Cy, 30, NULL, NULL]left_only3NULL[4, Dee, NULL, NULL, NULL]right_onlyNULL3[NULL, NULL, NULL, 40, legal]right_onlyNULL4[NULL, NULL, NULL, NULL, unknown] FULL OUTER JOIN factsfactvalueleftRowCount4rightRowCount5candidatePairCount20matchingPairCount3leftOnlyRowCount2rightOnlyRowCount2outputRowCount7duplicateMatchRows1nullKeyCandidatePairs8rowOrdermatched_left_source_then_left_only_then_right_only

Left side is padded

The classification table marks these rows as right_only.

class right only\text{class right only}

Summary

FULL OUTER JOIN preserves unmatched right rows too.

right preserved\text{right preserved}