DISTINCT
Test tables repeat the same values many times, so you often want the list of what exists instead of every row. DISTINCT gives you that list.
How it works
DISTINCT works on the rows SELECT has already built. It compares each whole output row with the others and keeps one row of each kind.
SELECT DISTINCTcolumns returns one row for each different combination of the listed columns.DISTINCTcompares the whole row.SELECT DISTINCT a, bkeeps every different pair ofaandb. It does not deduplicate each column on its own.DISTINCTtreatsNULLas one value, so allNULLvalues collapse into a single row.- Add
ORDER BYwhen you need the list in a readable order.
Common mistake
SELECT DISTINCT make, test_result returns up to one row per pair, so the list is longer than the list of makes. Adding a column to a DISTINCT query changes what counts as a duplicate.
Worked example
SELECT DISTINCT data_source
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-03-01' AND '2024-03-31'
ORDER BY data_sourceThe query reads the March 2024 tests and keeps each data_source value once.
Add a second column and the same query keeps each different pair of values.