LEFT JOIN Filters

WHERE Runs After the Join

A post-join filter reads already-joined rows.

highlighted = computed this step

WHERE runs after the join

A WHERE filter reads the joined rows after matching and padding are finished.

WHERE after join\text{WHERE after join}

Compare before and after

The LEFT JOIN has 6 rows before WHERE and 3 rows after the right-side present filter.

rows after WHERE=3\text{rows after WHERE}=3

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.

Joined rows before WHERE: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey Joined rows before WHERE: 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 after the LEFT JOIN: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey WHERE after the LEFT JOIN: 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]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 factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount3whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Read kept flags

The post-join filter table marks each joined row kept or dropped.

kept after join\text{kept after join}

Summary

The filter is not changing the match table; it is filtering joined rows.

filter joined rows\text{filter joined rows}