DISTINCT Rows

NULL Duplicates Group Together

NULL-shaped projected rows form one duplicate group.

highlighted = computed this step

NULL duplicates group together

For DISTINCT grouping in this model, NULL projected rows can be duplicates of each other.

NULL duplicate group\text{NULL duplicate group}

Read NULL-key rows

There are 2 projected rows whose DISTINCT key contains NULL.

NULL-key rows=2\text{NULL-key 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.

NULL duplicates group together: 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

One NULL representative

The two NULL city rows form one DISTINCT group.

one NULL group\text{one NULL group}

Summary

DISTINCT duplicate grouping treats matching NULL-shaped projected rows as one group here.

NULL grouped\text{NULL grouped}