DISTINCT — SQL Tutorial with Examples

What You'll Learn

DISTINCT removes duplicate rows from a result set. When applied to multiple columns, it returns unique COMBINATIONS of those columns — not unique values per column independently. COUNT(DISTINCT col) counts unique non-null values in a column.

Syntax

SELECT DISTINCT col FROM table;
SELECT DISTINCT col1, col2 FROM table;
SELECT COUNT(DISTINCT col) FROM table;

Example

-- Unique department IDs
SELECT DISTINCT department_id FROM employees;

-- Unique city + country combinations
SELECT DISTINCT city, country
FROM customers;

-- How many distinct cities do customers live in?
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

Beyond the Basics

Data in play — reference tables for this topic

-- customers (introduced by this topic)
-- id | name   | email             | city   | country | signup_date
-- 1  | Karl   | karl@example.com  | Berlin | Germany | 2023-01-10
-- 2  | Ines   | ines@example.com  | Berlin | Germany | 2023-02-14
-- 3  | Omar   | omar@example.com  | Berlin | Austria | 2023-05-30
-- 4  | Sofia  | sofia@example.com | London | UK      | 2023-08-22
-- 5  | Maike  | maike@example.com | NULL   | NULL    | 2023-11-05

-- employees (from Topic 1) — department_id only:
-- 1=Ada(1), 2=Grace(1), 3=Alan(2), 4=Edsger(NULL), 5=Barbara(3)

DISTINCT deduplicates combinations, not columns

SELECT DISTINCT city, country returns unique PAIRS: ('Berlin','Germany') appears once, but ('Berlin','Germany') and ('Berlin','Austria') are two different rows and both survive. The unit of uniqueness is the entire selected row — never each column separately.

-- ShopCo customers (from the Data in play panel):
-- id | name   | city   | country
-- 1  | Karl   | Berlin | Germany
-- 2  | Ines   | Berlin | Germany
-- 3  | Omar   | Berlin | Austria
-- 4  | Sofia  | London | UK
-- 5  | Maike  | NULL   | NULL

SELECT DISTINCT city, country FROM customers
ORDER BY country, city;
-- (Berlin, Austria), (Berlin, Germany), (London, UK), (NULL, NULL) — 4 rows:
-- the Berlin/Germany pair collapsed to one; both NULLs count as one row

If you need per-column uniqueness, DISTINCT cannot do it — that is GROUP BY (Topic 9) territory.

A DISTINCT that 'fixes' duplicates is hiding a bug

Duplicates in a result come from two places: the data legitimately contains them, or your query manufactured them — usually a JOIN (Topic 15) matching multiple rows per key. DISTINCT removes both kinds identically, so it can silently destroy real data while you patch a query bug.

-- Legitimate: the list of cities we operate in
SELECT DISTINCT city FROM customers;

-- Suspicious: 'every customer suddenly appears twice'
-- SELECT DISTINCT * FROM customers;
-- ^ masks a duplicate-import bug instead of surfacing it

Before adding DISTINCT, identify which rows are duplicated and why. DISTINCT is a claim about your data — not a cleanup tool.

COUNT(DISTINCT) counts values; COUNT counts rows

Four of our five customers have a city; two share Berlin. COUNT(*) says 5 (every row), COUNT(city) says 4 (non-NULL values), COUNT(DISTINCT city) says 2 (unique values). Three related questions, three different answers — picking the wrong one is a quiet analytics bug.

SELECT
  COUNT(*)             AS all_customers,       -- every row, NULL or not
  COUNT(city)          AS customers_with_city, -- skips Maike's NULL city
  COUNT(DISTINCT city) AS unique_cities        -- collapses duplicates
FROM customers;
-- 5 | 4 | 2

'How many customers?' → COUNT(*). 'How many different cities?' → COUNT(DISTINCT city). Say the question out loud, then choose.

Common Mistakes

Assuming DISTINCT applies per column separately. SELECT DISTINCT a, b returns unique (a, b) pairs, not unique a values AND unique b values independently. DISTINCT also has performance overhead on large datasets — prefer GROUP BY for complex deduplication.

Interview Tips

Interviewers sometimes ask to deduplicate with row priority (e.g., keep the most recent row). For that, use ROW_NUMBER() window function rather than DISTINCT.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. What is the unit of uniqueness for SELECT DISTINCT city, country?

2. ShopCo has 5 customers: Karl/Ines both (Berlin, Germany), Omar (Berlin, Austria), Sofia (London), Maike (NULL, NULL). How many rows does DISTINCT city, country return?

3. On ShopCo's customers, what do COUNT(*), COUNT(city), and COUNT(DISTINCT city) return?

4. A teammate adds DISTINCT and 'the duplicates are gone'. Why be suspicious?

5. SELECT DISTINCT department_id FROM employees is equivalent to…?

6. Why can DISTINCT be slow on large result sets?

Practice

Return each distinct order status exactly once.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Is DISTINCT slow?

It can be: deduplication generally requires sorting or hashing the whole result set, which defeats indexes and blocks streaming. If duplicates only exist because of a JOIN fan-out, fixing the JOIN is both faster and more correct than DISTINCT on top.

DISTINCT vs GROUP BY — which should I use?

For pure deduplication they are equivalent (most engines produce the same plan). GROUP BY exists to power aggregates (Topic 9) and HAVING (Topic 10); reserving DISTINCT for 'I want unique values' and GROUP BY for 'I want per-group numbers' keeps intent readable.