SQL Select Distinct

SELECT DISTINCT is the SQL keyword that removes duplicate rows from a query’s result set, keeping only unique combinations of the selected columns. It sits right next to SELECT and tells the database engine: give me each different value, or combination of values, only once, no matter how many times it appears in the underlying data. It’s one of the most common tools for exploring a table’s shape—finding out what categories, countries, or departments exist—before writing more complex aggregate queries.

Overview: How SELECT DISTINCT Works

A plain SELECT column1, column2 FROM table returns one output row for every row that matches the FROM/WHERE conditions, even if two rows end up looking identical once only certain columns are shown. Adding DISTINCT tells the engine to take that raw result set and collapse any rows that are completely identical across every selected column into a single row.

The key detail that trips people up: DISTINCT looks at the whole output row, not each column independently. SELECT DISTINCT category, supplier does not mean “unique categories” and “unique suppliers” separately—it means unique (category, supplier) pairs. If you add a third column that happens to be unique per row (like a primary key), DISTINCT effectively becomes a no-op, because no two rows will ever be fully identical anymore.

Under the hood, most engines (including SQLite) implement DISTINCT by building a temporary structure—often an ephemeral B-tree or hash index keyed on the full output row—and checking each new row against it before including it in the output. That means DISTINCT is not free: it requires extra work proportional to the number of rows scanned, and on large tables without a supporting index it can mean a full sort or hash of the entire result set. DISTINCT can also be combined with aggregate functions, as in COUNT(DISTINCT column) or SUM(DISTINCT column), where the values are deduplicated before being aggregated.

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1;

Part	Meaning
`DISTINCT`	Placed immediately after `SELECT`; applies to the combination of all listed columns
`column1, column2, ...`	The columns whose unique combinations you want returned
`WHERE condition`	Filters rows before deduplication happens
`ORDER BY`	Sorts the deduplicated rows; in standard SQL (and SQLite) it must reference columns that are part of the `DISTINCT` select list

Examples

These examples use a small self-contained inventory table so the exact rows and results are fully controlled.

Example 1: Basic DISTINCT on one column

CREATE TABLE inventory (
  id INTEGER,
  item_name TEXT,
  category TEXT,
  supplier TEXT
);

INSERT INTO inventory VALUES
 (1, 'Widget A', 'Hardware', 'Acme'),
 (2, 'Widget B', 'Hardware', 'Acme'),
 (3, 'Gadget C', 'Electronics', 'Zeta'),
 (4, 'Gadget D', 'Electronics', 'Acme'),
 (5, 'Tool E', 'Hardware', 'Zeta');

SELECT DISTINCT category FROM inventory;

Result:

category
Hardware
Electronics

Even though the table has 5 rows, only 2 distinct category values exist, so only 2 rows come back. Without an ORDER BY clause the row order isn’t guaranteed by the SQL standard—don’t rely on it.

Example 2: DISTINCT across multiple columns

SELECT DISTINCT category, supplier FROM inventory;

Result:

category	supplier
Hardware	Acme
Electronics	Zeta
Electronics	Acme
Hardware	Zeta

Here 4 rows come back out of the original 5, because rows 1 and 2 share the exact same (Hardware, Acme) pair and collapse into one, while every other pairing is unique. This shows that DISTINCT deduplicates the combination, not each column separately.

Example 3: DISTINCT and NULL values

CREATE TABLE inventory (
  id INTEGER,
  item_name TEXT,
  category TEXT,
  supplier TEXT
);

INSERT INTO inventory VALUES
 (1, 'Widget A', 'Hardware', 'Acme'),
 (2, 'Widget B', 'Hardware', 'Acme'),
 (3, 'Gadget C', 'Electronics', 'Zeta'),
 (4, 'Gadget D', 'Electronics', 'Acme'),
 (5, 'Tool E', 'Hardware', 'Zeta'),
 (6, 'Mystery Item', NULL, 'Acme'),
 (7, 'Extra Item', NULL, 'Zeta');

SELECT DISTINCT category FROM inventory;

Result:

category
Hardware
Electronics
NULL

Even though two rows (6 and 7) both have a NULL category, DISTINCT treats them as equal to each other and collapses them into a single NULL row—this is a special rule, since normally NULL = NULL is unknown, not true, in SQL comparisons. Now compare with counting:

SELECT COUNT(DISTINCT category) FROM inventory;

Result: a single row with value 2. Unlike plain DISTINCT in the select list, COUNT(DISTINCT column) ignores NULL entirely, so it only counts the two real category values, Hardware and Electronics.

How It Works Step by Step

DISTINCT fits into the logical query processing order like this:

FROM — the source table(s) are identified, and joins are resolved.
WHERE — rows are filtered.
GROUP BY / HAVING — if present, rows are grouped and aggregate filters applied.
SELECT — the output row is built from the selected columns/expressions.
DISTINCT — the built output rows are deduplicated, conceptually equivalent to grouping by every column in the select list with no aggregate function.
ORDER BY — the deduplicated rows are sorted.
LIMIT — the final row count is capped, if specified.

Because DISTINCT runs after the SELECT list is evaluated, it can deduplicate computed expressions too (for example SELECT DISTINCT price * 1.1 FROM products), not just raw column values.

Common Mistakes

Mistake 1: Including a unique column defeats DISTINCT

SELECT DISTINCT orders.order_id, customers.customer_name, customers.country
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

This looks like it should return one row per customer, but it doesn’t: order_id is unique per row, so no two rows are ever fully identical, and DISTINCT has no effect—every order still appears separately, even if the same customer placed more than one order. The fix is to drop the column that makes every row unique when your intent is to list distinct customers:

SELECT DISTINCT customers.customer_name, customers.country
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Now rows collapse to one per distinct customer who has placed at least one order, regardless of how many orders each one placed.

Mistake 2: ORDER BY on a column outside the DISTINCT list

CREATE TABLE inventory (
  id INTEGER,
  item_name TEXT,
  category TEXT,
  supplier TEXT
);

INSERT INTO inventory VALUES
 (1, 'Widget A', 'Hardware', 'Acme'),
 (2, 'Gadget C', 'Electronics', 'Zeta'),
 (3, 'Tool E', 'Hardware', 'Zeta');

SELECT DISTINCT category FROM inventory ORDER BY supplier;

Here supplier isn’t part of the selected/distinct column list, so the database can’t unambiguously sort the deduplicated category rows by it—this fails to execute in SQLite and in standard SQL more broadly. The fix is to only sort by columns that are actually part of the DISTINCT output (or include the column you want to sort by in the select list):

SELECT DISTINCT category, supplier FROM inventory ORDER BY supplier;

Best Practices

Only list the columns you actually need unique combinations of—adding an extra unique column (like an ID) silently defeats deduplication.
Don’t use DISTINCT as a patch to hide duplicate rows caused by an incorrect JOIN; fix the join condition or query logic instead.
Remember DISTINCT treats multiple NULLs in a column as equal to each other, collapsing them into one row—this differs from normal NULL comparison rules.
Use COUNT(DISTINCT column) to count unique non-null values directly instead of fetching all distinct rows and counting them yourself.
Keep ORDER BY columns inside the DISTINCT select list for correctness and portability across database engines.
On large tables, be aware that DISTINCT requires sorting or hashing the matching rows, which has a real performance cost; an index on the deduplicated columns can help.

Practice Exercises

Using a products table with columns product_id, product_name, price, category containing rows in the Hardware and Electronics categories, write a query that lists each distinct category. (Hint: expect exactly 2 rows back.)
Using an employees table with a department column, write one query that lists the distinct departments, and a second query using COUNT(DISTINCT department) that returns how many distinct departments exist.
Using the inventory table from Example 2 in this lesson, write a query that returns each distinct (category, supplier) pair sorted alphabetically by category, then by supplier.

Summary

SELECT DISTINCT removes duplicate rows based on the full combination of selected columns, not each column separately.
Logically, deduplication happens after the SELECT list is built and before ORDER BY/LIMIT are applied.
NULL values are treated as equal to each other under DISTINCT, so repeated NULLs collapse into a single row.
COUNT(DISTINCT column) counts unique non-null values and ignores NULL entirely, unlike plain DISTINCT.
DISTINCT is not a substitute for a correct JOIN condition—use it deliberately, not as a workaround.
ORDER BY columns must be part of the DISTINCT select list for the query to run correctly.

SQL Select Distinct

Overview: How SELECT DISTINCT Works

Syntax

Examples

Example 1: Basic DISTINCT on one column

Example 2: DISTINCT across multiple columns

Example 3: DISTINCT and NULL values

How It Works Step by Step

Common Mistakes

Mistake 1: Including a unique column defeats DISTINCT

Mistake 2: ORDER BY on a column outside the DISTINCT list

Best Practices

Practice Exercises

Summary

Table of Contents