SELECT DISTINCT in SQL

Single Column

SELECT DISTINCT on a single column returns all unique values in that column. Duplicate values are collapsed to one row. NULL values are treated as a single distinct value: if three rows have NULL in the column, one NULL row appears in the result.

-- All unique departments
SELECT DISTINCT department
FROM employees;

-- Result: Engineering, Marketing, Sales, HR
-- Even if 200 rows have department = 'Engineering',
-- it appears once in the output

Multiple Columns

SELECT DISTINCT with multiple columns deduplicates based on the combination of all listed columns. Two rows are duplicates only if every column matches. Adding more columns to the SELECT makes duplicates less likely. This is the most misunderstood behavior of DISTINCT.

-- Unique department + job_title combinations
SELECT DISTINCT department, job_title
FROM employees;

-- Two engineers with different titles are NOT duplicates
-- ('Engineering', 'Senior Engineer') and
-- ('Engineering', 'Staff Engineer') are two rows

-- DISTINCT applies to the ENTIRE row, not just the first column

COUNT(DISTINCT)

COUNT(DISTINCT column) counts unique non-NULL values in one pass. It is more efficient than a subquery with SELECT DISTINCT followed by COUNT(*) because the engine can count during deduplication without materializing an intermediate result set.

-- How many unique cities?
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

-- Per-department unique skill count
SELECT
  department,
  COUNT(DISTINCT skill) AS unique_skills
FROM employee_skills
GROUP BY department;

-- NULLs are NOT counted by COUNT(DISTINCT)

DISTINCT with ORDER BY

You can combine DISTINCT with ORDER BY. The ORDER BY columns must appear in the SELECT list when using DISTINCT (in most engines). This constraint exists because DISTINCT operates on the SELECT columns, and ordering by a column not in the output would be ambiguous.

-- Unique departments, sorted alphabetically
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

-- This FAILS in PostgreSQL and most engines:
-- SELECT DISTINCT department FROM employees ORDER BY hire_date;
-- ERROR: ORDER BY expression must appear in select list

How DISTINCT Works Under the Hood

The database engine has two strategies for deduplication: sorting and hashing. With sorting, the engine sorts all rows by the DISTINCT columns and then scans sequentially, emitting each row only when it differs from the previous one. With hashing, the engine builds a hash table of seen values and skips rows that already exist in the table. The query optimizer chooses the strategy based on data size, available memory, and existing indexes.

Indexes and DISTINCT

If an index exists on the DISTINCT column(s), the engine can use an index-only scan to retrieve unique values without reading the full table. This is significantly faster for large tables. In PostgreSQL, a B-tree index on department lets SELECT DISTINCT department use an Index Only Scan, reading only the index pages instead of the full table.

DISTINCT on Large Result Sets

DISTINCT is expensive when the number of unique values is close to the total number of rows. If 90% of rows are unique, DISTINCT does a lot of work (sorting or hashing millions of rows) and removes very few duplicates. In this case, consider whether DISTINCT is necessary. Often, DISTINCT is used as a band-aid for a query that produces unintended duplicates due to a missing join condition or an incorrect GROUP BY.

DISTINCT vs EXISTS for Semi-Joins

A common anti-pattern is using DISTINCT to remove duplicates caused by a JOIN. If you join orders to order_items and only want unique orders, SELECT DISTINCT o.* is wasteful because it joins all items then deduplicates. Using EXISTS or IN is faster because the engine stops after finding the first match per order.