DISTINCT: Remove Duplicates

"Which teams do our players belong to?" If you just SELECT team, MI appears twice because two players play for MI. You want each team once. DISTINCT removes the duplicates.

Definition

DISTINCT removes duplicate rows from the result, so every row returned is unique. It looks at all the columns in the SELECT list together — a row is a duplicate only if every selected column matches another row.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

DISTINCT is written once, right after SELECT, and applies to the whole row — not just the first column.

Example — every team only once

SELECT DISTINCT team
FROM players;

Execution order: 1️⃣ FROM → 2️⃣ SELECT team (6 values: RCB, MI, RR, CSK, DC, MI) → 3️⃣ DISTINCT removes the second MI → 5 rows.

Note: The order above is what the playground returns. SQL Server often returns DISTINCT results sorted (CSK, DC, MI, RCB, RR) because it removes duplicates by sorting — but that is a side-effect, not a promise. Add ORDER BY team if order matters.

Example — DISTINCT on two columns

SELECT DISTINCT team, country
FROM players;

How it works: both MI players have the pair (MI, India), so that pair appears once. India appears three times in the result — that's fine, because each combination (team, country) is different.

Example — counting unique values

SELECT COUNT(DISTINCT team) AS teams
FROM players;

COUNT(DISTINCT column) counts how many different values exist — 5 teams, even though there are 6 players.

DISTINCT vs GROUP BY

SELECT DISTINCT team FROM players and SELECT team FROM players GROUP BY team return the same teams. Use DISTINCT to simply de-duplicate; use GROUP BY when you also need aggregates (counts, sums) per value.

Common mistakes

• ❌ SELECT DISTINCT team, DISTINCT country → syntax error. ✅ One DISTINCT covers all columns: SELECT DISTINCT team, country.
• ❌ SELECT DISTINCT * FROM players to "remove duplicate teams" — every row has a unique id, so nothing is removed. ✅ Select only the columns you want unique.
• ❌ Using DISTINCT to hide duplicates created by a wrong JOIN. ✅ Fix the JOIN; DISTINCT on big results is expensive (it needs a sort or hash).

Interview tip

Q: Does DISTINCT apply to one column or the whole row? A: The whole row of selected columns. SELECT DISTINCT a, b returns each unique (a, b) combination once; a value of a can repeat if it pairs with different values of b.