DISTINCT: removing duplicate rows

SELECT DISTINCT removes duplicate rows from the result, where two rows are duplicates only if every selected column is the same.

Why it matters

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

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.

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.

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.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

How it works

  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.

Key points

Common mistakes

Not true: DISTINCT is the right fix when a join returns the same row several times.
In fact: Duplicates from a join usually mean the join itself is wrong, and DISTINCT only hides that. Fix the join condition instead.

In the interview

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.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.