Every selected column participates in the key.
Multi-column DISTINCT keys
With several selected columns, the whole projected row is the key.
whole projected row \text{whole projected row} whole projected row
Read multi-column keys
The key uses 2 columns and produces 4 distinct rows.
distinct rows = 4 \text{distinct rows}=4 distinct rows = 4
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.
Multi-column DISTINCT keys: bag input rows Multi-column DISTINCT keys: bag input rows id city status 1 Austin open 2 Austin open 3 Boston closed 4 Austin closed 5 NULL open 6 NULL open WHERE then SELECT projection WHERE then SELECT projection source input whereTruth projectedRow distinctRole 0 [1, Austin, open] TRUE [Austin, open] representative 1 [2, Austin, open] TRUE [Austin, open] duplicate 2 [3, Boston, closed] TRUE [Boston, closed] representative 3 [4, Austin, closed] TRUE [Austin, closed] representative 4 [5, NULL, open] TRUE [NULL, open] representative 5 [6, NULL, open] TRUE [NULL, open] duplicate DISTINCT groups DISTINCT groups key sources representativeSource duplicateCount [Austin, open] [0, 1] 0 1 [Boston, closed] [2] 2 0 [Austin, closed] [3] 3 0 [NULL, open] [4, 5] 4 1 DISTINCT output rows DISTINCT output rows city status Austin open Boston closed Austin closed NULL open DISTINCT facts DISTINCT facts fact value inputRowCount 6 whereKeptRows 6 whereDroppedRows 0 projectedRowCount 6 distinctRowCount 4 duplicateDropCount 2 nullKeyRows 2 selectColumnCount 2 representativeOrder first_seen_model_order_not_sql_order_claim
City plus status
Austin with open and Austin with closed are different projected rows.
both columns matter \text{both columns matter} both columns matter
Summary
DISTINCT compares every selected column together.
full key comparison \text{full key comparison} full key comparison