🧹SELECT DISTINCT

DISTINCT deduplicates whole projected rows

SELECT DISTINCT a, b keeps one row per unique (a, b) pair, not “distinct a and distinct b separately.” If you add a third column, you may “lose” deduplication visually because the tuple widens. NULL participates in DISTINCT comparisons in engine-specific ways, always sanity-check with small fixtures.

⚖️DISTINCT vs GROUP BY (same engine, different readability)

Both can dedup; GROUP BY forces you to name aggregates explicitly when you also want counts.

For simple projection dedup, DISTINCT is concise. When you also need “how many collapsed rows per key,” switch to GROUP BY with COUNT(*) so the grain is obvious in code review.

SQL

Without DISTINCT (duplicates remain)

SELECT tissue_type
FROM biosamples;

✓Illustrative result set

tissue_type
liver
liver
brain
liver

SQL

With DISTINCT

SELECT DISTINCT tissue_type
FROM biosamples
ORDER BY tissue_type;

✓Illustrative result set

tissue_type
brain
liver

Slapping DISTINCT after a bad join

Join fan-out duplicates rows; DISTINCT hides the explosion but still paid for the bigger intermediate bag, sometimes massively.

Fix join keys or pre-aggregate

Reduce rows in a CTE first, or aggregate to the correct grain before joining wide attributes back in.

🐘DISTINCT ON (PostgreSQL extension)

Pick one representative row per group, ORDER BY must define which row wins.

PostgreSQL extends the standard with DISTINCT ON (expr) to keep the first row per group after an ORDER BY that orders ties deterministically. Portable SQL often uses window functions instead, choose based on team dialect lock-in vs standards.

SQL

Keep latest reading per device

SELECT DISTINCT ON (device_id) device_id, reading_ts, temp_c
FROM cold_chain_readings
ORDER BY device_id, reading_ts DESC;

✓Illustrative result set

device_id	reading_ts	temp_c
D-1	2026-04-11 22:00	4.1
D-2	2026-04-11 21:55	5

Review question

Before merging: “If I removed DISTINCT, which duplicate row would appear, and is that row physically possible in our model?” If you cannot answer, DISTINCT is masking a bug.

TL;DR

SELECT DISTINCT deduplicates whole projected rows after WHERE/JOIN: it is not a substitute for fixing join grain. It often requires sort or hash dedup work (O(n log n) or O(n) memory). Prefer fixing the underlying one-to-many join or grouping when duplicates are logically impossible.