SR

DISTINCT

Collapse duplicate rows into unique values

Beginner 8 minDISTINCTduplicatesunique

Removing Duplicates

DISTINCT removes duplicate rows from your result set. It compares all selected columns together as a unit — a row is only a duplicate if every single column value matches another row. Here's how it works:

Full-row comparison — SELECT DISTINCT country gives unique countries. But SELECT DISTINCT country, city gives unique country-city pairs, so the same country can appear multiple times when paired with different city values.
COUNT(DISTINCT column) — More efficient than fetching all distinct rows and counting them externally. Counts unique non-NULL values directly inside the database engine without returning all the rows to your application.
Performance cost — DISTINCT scans and compares every row in the result, which can be slow on large tables. Use it when you specifically need deduplication, not as a band-aid for poorly written queries.
Knowledge Canvas

How DISTINCT Works

Remove duplicate rows from results

  • DISTINCT evaluates the entire row — all selected columns must match
  • Applied AFTER SELECT, BEFORE ORDER BY
  • Two NULLs are treated as equal by DISTINCT
  • Affects ALL columns in the SELECT list

Performance Impact

DISTINCT isn't free

  • DISTINCT requires sorting or hashing — can be slow on large sets
  • Often a sign of a bad join — fix the join instead
  • GROUP BY is sometimes more efficient than DISTINCT
  • Index on the DISTINCT columns helps performance

Common Mistakes

DISTINCT traps

  • DISTINCT on SELECT * rarely makes sense
  • Adding more columns makes rows "more unique" — fewer duplicates removed
  • DISTINCT in aggregate: COUNT(DISTINCT col) ≠ COUNT(col)
  • Can't DISTINCT on one column while showing others easily

DISTINCT vs GROUP BY

Two ways to deduplicate

DISTINCT: simple dedup
GROUP BY: dedup + aggregate
SELECT DISTINCT country
SELECT country, COUNT(*)
No aggregation possible
SUM, COUNT, AVG available
Syntax
Syntax Template
1-- Unique row combinations
2SELECT DISTINCT
3 column1,
4 column2
5FROM table;
6
7-- Count unique non-NULL values
8SELECT
9 COUNT(DISTINCT column)
10FROM table;
DISTINCTPlaced after SELECT, before column names
COUNT(DISTINCT col)Counts unique non-NULL values
Sample Data
customers
12 rows
idnamecountry
C1001Aarav SharmaIndia
C1002Sara ChenSingapore
C1003James WilsonUK
C1004Maria GarciaSpain
C1005Yuki TanakaJapan
C1006Priya PatelIndia
C1007Alex JohnsonUSA
C1008Chen WeiChina
C1009Emma BrownAustralia
C1010Omar HassanUAE
C1011Lena MullerGermany
C1012Ravi KumarIndia
products
10 rows
idnamecategorypricestock
P101Wireless MouseElectronics24.99150
P102Office DeskFurniture18925
P103LED Desk LampFurniture45.580
P104USB-C CableElectronics12.99500
P105Bluetooth SpeakerElectronics7960
P106Office ChairFurniture9515
P107Coffee MakerAppliances89.9940
P108Notebook SetStationery8.5200
P109Wireless HeadphonesElectronics15930
P110Standing DeskFurniture4258

Worked Example

List all unique countries from the customers table.

SQL
1SELECT DISTINCT
2 country
3FROM customers
4ORDER BY
5 country;
India appears 3 times in the data (Aarav, Priya, Ravi) but only once in the result. DISTINCT collapses all duplicates into a single row per unique value.
Output
10 rows
country
Australia
China
Germany
India
Japan
Singapore
Spain
UAE
UK
USA

Key Concepts

1DISTINCT compares the full row — all selected columns together
2SELECT DISTINCT country, city allows country repeats with different cities
3COUNT(DISTINCT col) is more efficient than DISTINCT + external count
4DISTINCT can be slow on large tables — use sparingly

Pro Tip

Data frequently contains repeated values. DISTINCT gives you clean, deduplicated lists — essential for analysis and reporting.

When to Use

Unique countries, product categories, order statuses, deduplicating email lists.

Challenge

Solve the problem below

Find the number of unique categories in the products table. Name the result column unique_categories.

Your Query

Previous

ALTER TABLE & DROP TABLE

Next

IN Operator