LEFT JOIN Filters

Right Value Filter After Join

Right-side equality filters run after padding.

highlighted = computed this step

Right value filters after join

A WHERE condition on a right-side value is checked after the LEFT JOIN rows exist.

right value WHERE\text{right value WHERE}

Read value-filter output

From 6 joined rows, the right-state filter keeps 2 rows and drops 4 rows.

right value kept=2\text{right value kept}=2

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

LEFT JOIN before value filter: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey LEFT JOIN before value filter: right tablecidorder_idstate1101paid1102open3103paid5104paidNULL105ghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.cidleft.nameright.cidright.order_idright.state1Ada1101paid1Ada1102open2BeaNULLNULLNULL3Cal3103paid4DeeNULLNULLNULLNULLNoKeyNULLNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, 101, paid]1yes[1, Ada, 1, 102, open]2yes[2, Bea, NULL, NULL, NULL]3yes[3, Cal, 3, 103, paid]4yes[4, Dee, NULL, NULL, NULL]5yes[NULL, NoKey, NULL, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount6whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source
WHERE right state is paid: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey WHERE right state is paid: right tablecidorder_idstate1101paid1102open3103paid5104paidNULL105ghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.cidleft.nameright.cidright.order_idright.state1Ada1101paid1Ada1102open2BeaNULLNULLNULL3Cal3103paid4DeeNULLNULLNULLNULLNoKeyNULLNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, 101, paid]1no[1, Ada, 1, 102, open]2no[2, Bea, NULL, NULL, NULL]3yes[3, Cal, 3, 103, paid]4no[4, Dee, NULL, NULL, NULL]5no[NULL, NoKey, NULL, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount2whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

NULL is not a match

Padded NULL right-side values do not pass the equality filter.

NULL not TRUE\text{NULL not TRUE}

Summary

This scoped compiler shows post-join filtering, not vendor-specific SQL planning.

finite post join filter\text{finite post join filter}