SQLite DISTINCT

The DISTINCT keyword removes duplicate rows from query results. Every unique combination of selected column values appears exactly once in the output.

The Problem DISTINCT Solves

TABLE: sales
────────────────────────────────────
id │ rep     │ region    │ product
───┼─────────┼───────────┼─────────
1  │ Alice   │ North     │ Pen
2  │ Bob     │ South     │ Book
3  │ Alice   │ North     │ Ruler
4  │ Carol   │ East      │ Pen
5  │ Bob     │ South     │ Pen
6  │ Alice   │ West      │ Book

-- Without DISTINCT: repeated regions appear
SELECT region FROM sales;
North
South
North     ← duplicate
East
South     ← duplicate
West

-- With DISTINCT: each region once
SELECT DISTINCT region FROM sales;
North
South
East
West

DISTINCT on One Column

SELECT DISTINCT rep FROM sales;

rep
──────
Alice
Bob
Carol

DISTINCT on Multiple Columns

When you select multiple columns, DISTINCT removes rows where ALL selected columns are identical. The uniqueness check applies to the full combination.

SELECT DISTINCT rep, region FROM sales;

rep   │ region
──────┼───────
Alice │ North
Bob   │ South
Carol │ East
Alice │ West

-- "Alice / North" appears once even though Alice made
-- two sales in the North region.

DISTINCT vs GROUP BY

Both DISTINCT and GROUP BY can deduplicate values. DISTINCT is simpler when you only need unique values. GROUP BY is better when you also want to aggregate data like counts or totals.

-- Same result, different use case:
SELECT DISTINCT region FROM sales;
SELECT region FROM sales GROUP BY region;

-- DISTINCT wins for simple deduplication.
-- GROUP BY wins when you need counts:
SELECT region, COUNT(*) AS sales_count
FROM sales
GROUP BY region;

region │ sales_count
───────┼────────────
East   │ 1
North  │ 2
South  │ 2
West   │ 1

DISTINCT with ORDER BY

SELECT DISTINCT region FROM sales ORDER BY region;

region
──────
East
North
South
West

DISTINCT with COUNT

-- How many unique reps made sales?
SELECT COUNT(DISTINCT rep) AS unique_reps FROM sales;

unique_reps
───────────
3

DISTINCT and NULL

Multiple NULL values in a column are treated as equal for DISTINCT purposes. Only one NULL appears in the output.

TABLE: contacts
─────────────────────
name   │ phone
───────┼──────────
Alice  │ 555-0100
Bob    │ NULL
Carol  │ NULL
Dan    │ 555-0200

SELECT DISTINCT phone FROM contacts;

phone
──────────
555-0100
NULL        ← only one NULL, even though two existed
555-0200

DISTINCT with WHERE and LIMIT

DISTINCT works alongside WHERE and LIMIT. WHERE filters first, then DISTINCT removes duplicates from what remains.

-- Unique regions where Alice made sales
SELECT DISTINCT region FROM sales
WHERE rep = 'Alice';

region
──────
North
West

-- Unique reps limited to first 2 alphabetically
SELECT DISTINCT rep FROM sales
ORDER BY rep
LIMIT 2;

rep
──────
Alice
Bob

DISTINCT with Expressions

You can apply DISTINCT to computed columns, not just raw column values. SQLite evaluates the expression first, then removes duplicate results.

-- Unique first letters of product names
SELECT DISTINCT UPPER(SUBSTR(product, 1, 1)) AS first_letter
FROM sales
ORDER BY first_letter;

first_letter
────────────
B
P
R

-- Unique (rep, first-letter-of-product) combinations
SELECT DISTINCT rep, UPPER(SUBSTR(product,1,1)) AS initial
FROM sales
ORDER BY rep, initial;

rep   │ initial
──────┼────────
Alice │ P
Alice │ R
Bob   │ B
Bob   │ P
Carol │ P

DISTINCT in Subqueries

-- Find reps who sold in more than one region
SELECT rep
FROM (
  SELECT DISTINCT rep, region FROM sales
) rep_regions
GROUP BY rep
HAVING COUNT(*) > 1;

rep
──────
Alice
Bob

Performance Consideration

DISTINCT requires SQLite to sort or hash the result set to identify duplicates. On large tables, this adds processing time. If an index covers the columns you are selecting, SQLite can read them in sorted order and remove duplicates efficiently without a full sort pass. Without an index, SQLite must sort all matching rows in memory.

-- Fast if an index on (rep) exists:
SELECT DISTINCT rep FROM sales;

-- Slower without an index on large tables:
SELECT DISTINCT long_text_column FROM big_table;

-- Create an index if you run DISTINCT on a column frequently:
CREATE INDEX idx_rep ON sales(rep);

Key Takeaways

SELECT DISTINCT col returns each unique value once
DISTINCT on multiple columns compares full-row uniqueness
Use COUNT(DISTINCT col) to count unique values in a column
Multiple NULLs collapse to one NULL in DISTINCT results
DISTINCT works on expressions and computed columns, not just raw column names
Use GROUP BY instead of DISTINCT when you need aggregate statistics alongside the unique values
An index on the selected column speeds up DISTINCT on large tables

Previous lesson

Back to course

Next lesson