DISTINCT Rows

WHERE before DISTINCT

WHERE filters before projection and duplicate removal.

highlighted = computed this step

WHERE before DISTINCT

WHERE filters source rows before projection and DISTINCT.

WHERE then projection then DISTINCT\text{WHERE then projection then DISTINCT}

Read pipeline facts

WHERE drops 2 rows before DISTINCT returns 2 rows.

WHERE dropped rows=2\text{WHERE dropped rows}=2

DISTINCT examples are tiny finite bag-to-table transforms; this model shows first-seen representatives for deterministic display and does not claim product SQL output ordering.

WHERE before DISTINCT: bag input rowsidcitystatus1Austinopen2Austinopen3Bostonclosed4Austinclosed5NULLopen6NULLopen WHERE then SELECT projectionsourceinputwhereTruthprojectedRowdistinctRole0[1, Austin, open]TRUE[Austin]representative1[2, Austin, open]TRUE[Austin]duplicate2[3, Boston, closed]FALSE[]not_projected3[4, Austin, closed]FALSE[]not_projected4[5, NULL, open]TRUE[NULL]representative5[6, NULL, open]TRUE[NULL]duplicate DISTINCT groupskeysourcesrepresentativeSourceduplicateCount[Austin][0, 1]01[NULL][4, 5]41 DISTINCT output rowscityAustinNULL DISTINCT factsfactvalueinputRowCount6whereKeptRows4whereDroppedRows2projectedRowCount4distinctRowCount2duplicateDropCount2nullKeyRows2selectColumnCount1representativeOrderfirst_seen_model_order_not_sql_order_claim

Closed rows never project

Rows dropped by WHERE do not enter the projected duplicate groups.

filter first\text{filter first}

Summary

The pipeline order is visible: filter, project, then remove duplicates.

visible pipeline\text{visible pipeline}