DISTINCT: removing duplicate rows

Step 1 Understand it

Why you need it. "Which cities do our departments sit in?" lists London twice. "Which customers ordered in January?" lists Priya three times. DISTINCT gives each answer once.

Watch it happen

Picture it

flowchart LR
  A["London<br/>Manchester<br/>London<br/>Leeds"] -->|"DISTINCT"| B["Leeds<br/>London<br/>Manchester"]

The idea

DISTINCT goes straight after SELECT and applies to the whole row you selected, not to one column. SELECT DISTINCT location compares only location, so London appears once. SELECT DISTINCT name, customer_id compares both, so two Toms with different ids are both kept. For DISTINCT, NULLs count as equal to each other, so many NULLs become one.

The shape of it

SELECT DISTINCT column1, column2
FROM table_name;

Words in lower case are the parts you fill in.

How it works, step by step

1. The database builds the rows with just the selected columns.
2. It compares whole rows: two rows are duplicates only if every selected column matches.
3. It keeps one copy of each distinct row.
4. ORDER BY then sorts what is left.

Analogy

Collecting football stickers: when you sort your pile, you keep one of each and put the swaps aside. Two stickers count as the same only if they are completely identical.

Where the analogy breaks

With stickers you can choose which copy to keep. DISTINCT can't: it keeps one row of each and throws the rest away, without remembering how many there were. If you need the count, use GROUP BY.

Worked examples

SELECT DISTINCT location
FROM departments
ORDER BY location;

SELECT DISTINCT name, email
FROM customers
ORDER BY name;

SELECT DISTINCT customer_id, name
FROM customers
ORDER BY customer_id;

Must-know points

• DISTINCT applies to the whole selected row, never to just one column.
• Adding a unique column (like an id) to a DISTINCT query makes it do nothing.
• NULLs count as equal for DISTINCT: many NULLs become one.
• COUNT(DISTINCT column) counts the different values — Phase 2.
• DISTINCT has to compare every row, which costs time on big results.

Common mistakes

• Writing SELECT DISTINCT(name), email and thinking only name is distinct. The brackets do nothing; the whole row is compared.
• Using DISTINCT to hide duplicates caused by a wrong join. Fix the join instead (Phase 3).
• Using DISTINCT when you actually need a count per value. That is GROUP BY.

In other databases

• PostgreSQL has DISTINCT ON (column), which keeps the first row for each value of that column — handy for "latest order per customer". Elsewhere, use ROW_NUMBER (Phase 5).

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL SELECT DISTINCT

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL DISTINCT

  Mode, free

• Practice

  SQLBolt — Lesson 4: Filtering and sorting Query results

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why SELECT DISTINCT customer_id, name FROM customers still shows Tom twice.

   Show answer

   DISTINCT removes a row only when every selected column matches another row. Tom has two sign-ups, with customer_id 2 and 6. So the rows are (2, Tom) and (6, Tom): the names match, but the ids don't, so they are different rows and both stay.

   Drop customer_id and select just name, and the two Toms become one.

2. Writefrom a blank page, then run it

   Write a query for the list of customer ids who have placed at least one order, each id once, sorted.

   Show answer

   orders has one row per order, so customer 1 appears five times. DISTINCT keeps each id once. Customers 4 (Omar) and 6 (the second Tom) never ordered, so they are not in orders at all.

   sqlCopy

   SELECT DISTINCT customer_id
   FROM orders
   ORDER BY customer_id;

   Try it

   4 rows

   customer_id
   1
   2
   3
   5

3. Predictsay it before you run it

   How many rows does SELECT DISTINCT status FROM orders; return?

   Show answer

   4 rows: cancelled, delivered, pending and shipped. There are 10 orders but only four different statuses. (ORDER BY is added below so the order is fixed.)

   sqlCopy

   SELECT DISTINCT status
   FROM orders
   ORDER BY status;

   Try it

   4 rows

   status
   cancelled
   delivered
   pending
   shipped

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   How many rows does SELECT DISTINCT dept_id FROM employees; return, and does the NULL count as a row?

   Show answer

   4 rows: 1, 2, 3 and NULL. Yes, the NULL counts.

   DISTINCT treats all NULLs as one group, even though NULL = NULL is not true in a WHERE. Gus's missing department gives one NULL row. Department 4 (HR) is not in the list, because nobody works there.

   sqlCopy

   SELECT DISTINCT dept_id
   FROM employees
   ORDER BY dept_id;

   Try it

   4 rows

   dept_id
   NULL
   1
   2
   3

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

SELECT DISTINCT removes duplicate rows from the result, comparing all the selected columns together, with NULLs treated as equal. I use it for lists of unique values, but if I find myself adding DISTINCT to fix unexpected duplicates, I go back and check the join, because DISTINCT can hide a fan-out that also breaks sums and counts.

Follow-up questions

Tick each check when you can do it without notes