DISTINCT Rows

Projection before DISTINCT

SELECT projection happens before duplicate removal.

highlighted = computed this step

Projection before DISTINCT

SELECT chooses the output columns before DISTINCT removes repeats.

project then distinct\text{project then distinct}

Read projected rows

Projection makes 6 projected rows before duplicates are removed.

projected rows=6\text{projected rows}=6

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.

Projection before DISTINCT: bag input rowsidcitystatus1Austinopen2Austinopen3Bostonclosed4Austinclosed5NULLopen6NULLopen WHERE then SELECT projectionsourceinputwhereTruthprojectedRowdistinctRole0[1, Austin, open]TRUE[Austin]representative1[2, Austin, open]TRUE[Austin]duplicate2[3, Boston, closed]TRUE[Boston]representative3[4, Austin, closed]TRUE[Austin]duplicate4[5, NULL, open]TRUE[NULL]representative5[6, NULL, open]TRUE[NULL]duplicate DISTINCT groupskeysourcesrepresentativeSourceduplicateCount[Austin][0, 1, 3]02[Boston][2]20[NULL][4, 5]41 DISTINCT output rowscityAustinBostonNULL DISTINCT factsfactvalueinputRowCount6whereKeptRows6whereDroppedRows0projectedRowCount6distinctRowCount3duplicateDropCount3nullKeyRows2selectColumnCount1representativeOrderfirst_seen_model_order_not_sql_order_claim

Only city is kept

The id and status columns are not part of the DISTINCT key in this lesson.

selected columns form key\text{selected columns form key}

Summary

DISTINCT compares the projected rows, not the original full input rows.

compare projected rows\text{compare projected rows}