DISTINCT vs GROUP BY

Concepts covered: Deduplication, Grouping, Counting, Set Combination

Start with the case where the copies agree in every column. A customer file was assembled from 2 regional exports that overlapped, so imported_customers holds 6 rows for 3 customers. The ask is one copy of each row, and the whole answer is one keyword. Watch what the keyword actually compares, because the rows it keeps are the ones that trip people in the next question.

SELECT DISTINCT
  customer_id,
  email,
  segment
FROM imported_customers

Of the 6 rows that go in, 4 come out. Customer 101's 2 rows agree on customer_id and email and carry the same segment, so they fold into 1, and so do customer 102's. Customer 103 keeps both rows, because one says retail and the other wholesale, and DISTINCT compares the entire selected row: a difference in any 1 of the 3 columns keeps a row alive. Drop segment from the select list and the same query returns 3 rows. The columns you list are the definition from the previous section, written into the query.

DISTINCT runs after the select list is computed, so it compares the expressions you wrote rather than the stored columns. Postgres compares case-sensitively by default, as do Snowflake and BigQuery, so there SELECT DISTINCT email keeps Ana@shopmail.com and ana@shopmail.com as 2 values; the MySQL and SQL Server defaults compare case-insensitively and fold them. SELECT DISTINCT LOWER(email) folds them on every engine, which is why the normalised expression is the one to write. The same rule makes SELECT DISTINCT * compare every stored column, including the ones that were never part of the key, which is the mistake the last section of this lesson takes apart.

GROUP BY folds the same rows

SELECT
  customer_id,
  email,
  segment,
  COUNT(*) AS copies
FROM imported_customers
GROUP BY customer_id, email, segment;

The 4 rows are the ones DISTINCT returned, because both constructs do the same work: every column in the list becomes part of a group key, and rows with equal keys fold into 1. Most optimizers produce the same hash aggregate plan for either form, so no performance argument separates them on exact copies. What GROUP BY adds is the aggregate column: copies shows that 101 and 102 arrived twice and 103 once per segment, which is the first evidence of how the overlap happened. If the import had also stamped a loaded_at on each row, GROUP BY could carry MIN(loaded_at) beside each survivor, a column outside the key that DISTINCT has no way to express.

One constraint follows from that. Under DISTINCT, an ORDER BY column has to appear in the select list, because the engine holds no single value of an unselected column for a folded row. GROUP BY lifts the limit for aggregates, so ORDER BY MIN(loaded_at) is legal after a group and has no equivalent under DISTINCT. When the interviewer asks for the earliest copy's load time beside each surviving row, only the second form can produce it.

3 forms that return the same rows

• SELECT DISTINCT customer_id, email, segment: reads as deduplication, which is what you are doing
• SELECT customer_id, email, segment ... GROUP BY customer_id, email, segment: the same rows, reads as aggregation
• SELECT ... UNION SELECT ...: UNION without ALL removes copies across both branches and within each one

Pick the form that names the intent and say why: 'I'm using DISTINCT because it reads as deduplication; GROUP BY would read as aggregation, and I reach for it when I want a count beside each surviving row.' That sentence tells the interviewer you know the forms are equivalent and chose on purpose. Writing GROUP BY with no aggregate and no comment reads as not knowing DISTINCT exists, and writing UNION against a single source reads as an accident.

NULL collapses with NULL

Both forms treat 2 NULLs in the same column as equal for grouping, even though NULL = NULL evaluates to UNKNOWN in a WHERE or a join. 2 rows with a NULL email and the same customer_id and segment fold into 1 under DISTINCT and under GROUP BY. That is the behaviour you want for a cleanup, and it deserves one sentence out loud, because the same 2 rows would refuse to match in a self-join written to find them. The interviewer hears that you know where NULL behaves one way and where it behaves the other.

DISTINCT is the right reach when

• The copies agree in every selected column
• Any copy can survive, so no ordering is needed
• The cleanup is a one-off on a file or an ad-hoc query
• You want the query to read as what it does

GROUP BY is the better fit when

• You want a count of copies beside each surviving row
• A column outside the key needs an aggregate, such as the earliest load time
• The result feeds a HAVING filter on the count
• Deduplication is one step of a larger aggregation anyway

About this lesson

This section is part of the Deduplication: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.