LEFT JOIN Filters

WHERE Drops Padded Rows

Right-side WHERE checks can remove NULL-padded rows.

highlighted = computed this step

WHERE can drop padded rows

A padded row has NULL in the right-side columns, so a right-side present test does not keep it.

NULL padding can be filtered\text{NULL padding can be filtered}

Read dropped padded rows

The post-join filter keeps 3 rows and drops 3 joined rows.

dropped rows=3\text{dropped rows}=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.

WHERE can drop padded rows: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey WHERE can drop padded rows: 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

The pitfall

A WHERE condition on the right side can remove the NULL-extended rows that made the join left-preserving.

right side WHERE pitfall\text{right side WHERE pitfall}

Summary

The left row was preserved by the join, then removed by WHERE.

preserved then filtered\text{preserved then filtered}