Question 1

Which row combinations collapse?

Accepted Answer

DISTINCT applies to the entire selected row - a row is a duplicate only if all selected column values match. Rows 1 and 2 are both (Alice, shipped) - that pair collapses to one row. The three unique (customer, status) combinations are: (Alice, shipped), (Alice, pending), (Bob, shipped). Result: 3 rows.

Question 2

How many rows does deduplication keep?

Accepted Answer

DISTINCT removes duplicate values from the result. Alice appears twice in the table but only once in the output. The result contains one row per unique customer value: Alice, Bob, Carol.

Question 3

Two NULLs walk into a SELECT DISTINCT...

Accepted Answer

For deduplication purposes, DISTINCT treats all NULLs as equal - multiple NULL values collapse into a single NULL row. The two NULL manager_id rows collapse to one. The distinct values are: NULL and 1. This is one case where NULL comparison behaves as equality, unlike WHERE where NULL = NULL is UNKNOWN.

Question 4

When would you pick GROUP BY over DISTINCT?

Accepted Answer

For a single non-aggregate column, DISTINCT and GROUP BY produce identical results. The practical difference is extensibility: GROUP BY lets you add COUNT(*), SUM(), and other aggregates alongside the grouped column; DISTINCT does not. Neither guarantees any particular row order without an explicit ORDER BY. Both handle NULLs the same way - one NULL group.

Question 5

Does NULL count as a unique value?

Accepted Answer

COUNT(DISTINCT column) counts unique non-NULL values. The distinct customers are Alice and Bob - that is 2. NULL is excluded because COUNT(column) always ignores NULLs, whether or not DISTINCT is used. Compare: COUNT(*) = 4 (all rows), COUNT(customer) = 3 (non-NULL), COUNT(DISTINCT customer) = 2 (unique non-NULL).

Question 6

Two independent COUNT(DISTINCT) in one query

Accepted Answer

COUNT(DISTINCT product) counts unique product values: Widget and Gadget = 2. COUNT(DISTINCT region) counts unique region values: East and West = 2. Both aggregates run independently across all rows. The result is a single row with two counts.

Question 7

Does DISTINCT scope to the first column?

Accepted Answer

DISTINCT is not a function - it applies to the entire row, not just the first column. A row is a duplicate only if all selected column values are the same. Two employees named 'Alice Johnson' and 'Alice Smith' have the same first_name but different last_name - they are NOT duplicates and both appear. This surprises many developers who expect DISTINCT to operate per-column.

Question 8

Which query computes totals per customer?

Accepted Answer

Query A mixes DISTINCT with SUM(amount) without a GROUP BY - the database does not know how to group the rows, so it either errors or returns a single aggregate over all rows (depending on the engine). Query B is the correct approach: GROUP BY customer groups the rows, then SUM(amount) aggregates within each group. When you need aggregates per group, always use GROUP BY, not DISTINCT.

Distinct