SQL DISTINCT: How SELECT DISTINCT Works (With Examples)

By Matthew Rathbone · November 14, 2026

The SQL DISTINCT keyword goes right after SELECT and tells the database to drop duplicate rows from the result. If a column holds Boston, Denver, Boston, Denver, Denver, SELECT DISTINCT gives you Boston, Denver. It is the quickest way to answer "which values are in this column?"

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

Jump to: how it works · sample data · examples · multiple columns · NULLs · COUNT DISTINCT · DISTINCT with ORDER BY · DISTINCT vs GROUP BY · DISTINCT ON · DISTINCT vs UNIQUE · IS DISTINCT FROM · performance · mistakes · FAQ

What does DISTINCT do in SQL?

A normal SELECT returns one output row for every input row that matches. SELECT DISTINCT does the same work, then looks at the finished rows and keeps only one copy of each. Two rows count as duplicates when every selected column has the same value.

Three things follow from that, and they explain almost every DISTINCT surprise:

1. DISTINCT applies to the whole row, not one column. SELECT DISTINCT customer, city returns unique pairs, not unique customers.
2. DISTINCT never changes your data. It only hides duplicates in the result. To delete them from the table, see how to remove duplicates in SQL.
3. DISTINCT treats NULLs as equal to each other, so all the NULLs collapse into a single row.

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. The PostgreSQL-only parts (DISTINCT ON and the ORDER BY error) were run on PostgreSQL 16. The core syntax is the same in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, city TEXT,
                     product TEXT, amount NUMERIC, order_date DATE);
INSERT INTO orders VALUES
 (1,  'Anna',  'Boston', 'Keyboard',  90, '2026-01-05'),
 (2,  'Ben',   'Denver', 'Mouse',     25, '2026-01-12'),
 (3,  'Anna',  'Boston', 'Monitor',  210, '2026-01-20'),
 (4,  'Carla', 'Denver', 'Keyboard',  45, '2026-02-02'),
 (5,  'Ben',   'Denver', 'Mouse',     25, '2026-02-14'),
 (6,  'Dev',   'Austin', 'Monitor',  210, '2026-02-21'),
 (7,  'Anna',  'Boston', 'Mouse',     25, '2026-03-03'),
 (8,  'Erin',  NULL,     'Keyboard',  45, '2026-03-09'),
 (9,  'Carla', 'Denver', 'Monitor',  210, '2026-03-15'),
 (10, 'Finn',  NULL,     'Mouse',     25, '2026-03-28');

Ten orders from six customers in three cities. Erin and Finn have no city on file.

SQL SELECT DISTINCT examples

1. Get the unique values in a column

Without DISTINCT you get one city per order, repeats and all:

SELECT city FROM orders;

city
------
Boston
Denver
Boston
Denver
Denver
Austin
Boston
NULL
Denver
NULL
(10 rows)

Add DISTINCT and each city appears once:

SELECT DISTINCT city FROM orders;

city
------
Boston
Denver
Austin
NULL
(4 rows)

Ten rows became four. The two NULLs became one NULL row, which is easy to miss. More on that in the NULL section.

2. DISTINCT with WHERE

WHERE filters the rows first, then DISTINCT removes duplicates from what is left. Which cities have bought a monitor?

SELECT DISTINCT city
FROM orders
WHERE product = 'Monitor';

city
------
Boston
Austin
Denver
(3 rows)

3. Sort the unique values

The order of a DISTINCT result is not guaranteed, so add ORDER BY when it matters. Here we also drop the NULL:

SELECT DISTINCT city
FROM orders
WHERE city IS NOT NULL
ORDER BY city;

city
------
Austin
Boston
Denver
(3 rows)

4. DISTINCT on an expression

DISTINCT works on whatever the SELECT list produces, including calculated values. Which months have orders?

SELECT DISTINCT strftime('%Y-%m', order_date) AS month
FROM orders;

month
-------
2026-01
2026-02
2026-03
(3 rows)

On other databases swap the date function: DATE_FORMAT(order_date, '%Y-%m') in MySQL, TO_CHAR(order_date, 'YYYY-MM') in PostgreSQL and Oracle, FORMAT(order_date, 'yyyy-MM') in SQL Server. The same trick cleans up inconsistent text: SELECT DISTINCT UPPER(product) treats "mouse" and "Mouse" as one value.

SQL DISTINCT on multiple columns

List more than one column and DISTINCT returns each unique combination. Which customers live in which city?

SELECT DISTINCT customer, city
FROM orders;

customer | city
---------+-------
Anna     | Boston
Ben      | Denver
Carla    | Denver
Dev      | Austin
Erin     | NULL
Finn     | NULL
(6 rows)

Anna ordered three times but appears once, because all three of her rows have the same customer and city. Now look at what happens with product and price:

SELECT DISTINCT product, amount
FROM orders;

product  | amount
---------+-------
Keyboard | 90
Mouse    | 25
Monitor  | 210
Keyboard | 45
(4 rows)

Keyboard shows up twice because it sold at two different prices. The pairs (Keyboard, 90) and (Keyboard, 45) are different, so both stay. If you wanted one row per product, you would need to decide which price to show, and that is a job for GROUP BY (for example MAX(amount)).

DISTINCT(column) does not mean "distinct on one column"

A very common mistake is writing parentheses to try to make DISTINCT apply to one column only:

SELECT DISTINCT(customer), product
FROM orders;

customer | product
---------+---------
Anna     | Keyboard
Ben      | Mouse
Anna     | Monitor
Carla    | Keyboard
Dev      | Monitor
Anna     | Mouse
Erin     | Keyboard
Carla    | Monitor
Finn     | Mouse
(9 rows)

Anna still appears three times. DISTINCT is not a function. The parentheses just wrap customer in a pointless group, and DISTINCT still applies to the whole row (customer, product). To get one row per customer with some of their other details, use DISTINCT ON in PostgreSQL or ROW_NUMBER() anywhere else.

How DISTINCT handles NULL

In a WHERE clause, NULL = NULL is not true (see NULL values in SQL). DISTINCT is different: it treats all NULLs as the same value, so they collapse into one row. That is why example 1 returned a single NULL even though two orders had no city.

The same rule applies across multiple columns. In PostgreSQL:

SELECT DISTINCT a, b
FROM (VALUES (1, NULL::int), (1, NULL), (NULL, NULL)) AS t(a, b);

  a   |  b
------+------
    1 | NULL
 NULL | NULL
(2 rows)

Every major database behaves this way. If you do not want the NULL row, filter it out with WHERE column IS NOT NULL, as in example 3. Where the NULL lands when you sort varies: SQLite, MySQL and SQL Server put NULLs first in ascending order, PostgreSQL and Oracle put them last.

Counting distinct values

To count unique values instead of listing them, put DISTINCT inside COUNT:

SELECT COUNT(city)          AS cities_total,
       COUNT(DISTINCT city) AS cities_unique
FROM orders;

cities_total | cities_unique
-------------+--------------
8            | 3
(1 row)

COUNT(DISTINCT city) ignores NULLs, so it returns 3 even though SELECT DISTINCT city returned 4 rows. To count distinct combinations of several columns, count the rows of a DISTINCT subquery:

SELECT COUNT(*)
FROM (SELECT DISTINCT customer, city FROM orders) AS t;

COUNT(*)
--------
6

DISTINCT works inside other aggregates too. SUM(DISTINCT amount) adds each different amount once: here it returns 370 (25 + 45 + 90 + 210) instead of the real total of 910. That is rarely what you want, so be careful with it. The full guide to SQL COUNT DISTINCT covers counting per group, multiple columns and the differences between databases.

DISTINCT with ORDER BY

Suppose you want each customer once, listed in the order they first bought something. This looks reasonable:

SELECT DISTINCT customer
FROM orders
ORDER BY order_date;

PostgreSQL, SQL Server and MySQL 5.7+ (with the default ONLY_FULL_GROUP_BY mode) reject it. PostgreSQL says:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

The error makes sense once you see the problem: Anna has three order dates, and after DISTINCT squashes her to one row, the database cannot know which date to sort her by. SQLite runs the query anyway and quietly picks one, which is worse because it can give you a wrong order without telling you.

The fix is to say which date you mean. Use GROUP BY with an aggregate:

SELECT customer, MIN(order_date) AS first_order
FROM orders
GROUP BY customer
ORDER BY first_order;

customer | first_order
---------+------------
Anna     | 2026-01-05
Ben      | 2026-01-12
Carla    | 2026-02-02
Dev      | 2026-02-21
Erin     | 2026-03-09
Finn     | 2026-03-28
(6 rows)

Rule of thumb: with SELECT DISTINCT, only sort by columns that are in the select list.

DISTINCT vs GROUP BY

For listing unique values, these two queries return the same rows:

SELECT DISTINCT city FROM orders;
SELECT city FROM orders GROUP BY city;

Most databases also run them with the same plan, so pick the one that says what you mean:

The moment you need a number per value (orders per city, spend per customer) switch to GROUP BY. Using both at once (SELECT DISTINCT city, COUNT(*) ... GROUP BY city) is redundant, because GROUP BY already returns one row per group.

PostgreSQL DISTINCT ON: one row per group

A frequent real question is "give me each customer's most recent order". Plain DISTINCT cannot do it, because you want one row per customer but several columns from that row. PostgreSQL has an extension for exactly this, DISTINCT ON:

SELECT DISTINCT ON (customer) customer, product, amount, order_date
FROM orders
ORDER BY customer, order_date DESC;

 customer | product  | amount | order_date
----------+----------+--------+------------
 Anna     | Mouse    |     25 | 2026-03-03
 Ben      | Mouse    |     25 | 2026-02-14
 Carla    | Monitor  |    210 | 2026-03-15
 Dev      | Monitor  |    210 | 2026-02-21
 Erin     | Keyboard |     45 | 2026-03-09
 Finn     | Mouse    |     25 | 2026-03-28
(6 rows)

DISTINCT ON keeps the first row for each value in the parentheses, and ORDER BY decides which row is first. The ORDER BY must start with the same columns as the DISTINCT ON list.

MySQL, SQL Server, Oracle and SQLite do not support DISTINCT ON. Use the ROW_NUMBER() window function instead, which works everywhere (including PostgreSQL) and gives the same result:

SELECT customer, product, amount, order_date
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) AS rn
  FROM orders
) AS t
WHERE rn = 1;

customer | product  | amount | order_date
---------+----------+--------+-----------
Anna     | Mouse    | 25     | 2026-03-03
Ben      | Mouse    | 25     | 2026-02-14
Carla    | Monitor  | 210    | 2026-03-15
Dev      | Monitor  | 210    | 2026-02-21
Erin     | Keyboard | 45     | 2026-03-09
Finn     | Mouse    | 25     | 2026-03-28
(6 rows)

Our SQL window functions guide explains how PARTITION BY and ROW_NUMBER() work.

DISTINCT vs UNIQUE

The two words get mixed up because they sound alike, but they do different jobs:

• DISTINCT is part of a query. It removes duplicates from one result and leaves the table alone.
• UNIQUE is a constraint on a table. It stops duplicate values from being stored in the first place. See the UNIQUE constraint.

If you find yourself adding DISTINCT to every query against a table, that table may need a UNIQUE constraint (or a clean-up). One exception: Oracle accepts SELECT UNIQUE as an old synonym for SELECT DISTINCT. Other databases do not, so stick with DISTINCT.

IS DISTINCT FROM

A different feature with a similar name. a IS DISTINCT FROM b is a NULL-safe "not equal": it treats NULL as a normal value instead of returning unknown. Compare:

SELECT id, customer, city FROM orders WHERE city <> 'Boston';
-- 5 rows: Ben, Carla, Ben, Dev, Carla (Erin and Finn are missing)

SELECT id, customer, city FROM orders WHERE city IS DISTINCT FROM 'Boston';
-- 7 rows: the same five plus Erin and Finn, whose city is NULL

<> skips the NULL rows because NULL <> 'Boston' is unknown, not true. IS DISTINCT FROM counts them as different. It is supported by PostgreSQL, SQL Server 2022+ and SQLite 3.39+. In MySQL use NOT (city <=> 'Boston').

Is SELECT DISTINCT slow?

DISTINCT has to compare rows, so the database sorts or hashes the result first. You can see it in SQLite's query plan:

EXPLAIN QUERY PLAN SELECT DISTINCT city FROM orders;

SCAN orders
USE TEMP B-TREE FOR DISTINCT

On small or medium results this costs almost nothing. It matters on millions of rows, and the usual fixes are:

• Index the column. With an index on city the database can read values already in order and skip the temporary sort.
• Select fewer columns. SELECT DISTINCT * compares every column of every row.
• Do not use DISTINCT to hide a bad join. If a JOIN returns each customer five times because they have five orders, adding DISTINCT throws away work the database already did. Use EXISTS instead (see SQL subqueries) so it never builds the duplicates.
• Use UNION ALL when you can. UNION does an implicit DISTINCT across both queries. If you know the parts do not overlap, UNION ALL skips that step. See UNION in SQL.

More tips in how to improve SQL query performance.

DISTINCT by database

Common mistakes

1. Expecting DISTINCT to work on one column. It applies to every selected column together, with or without parentheses.
2. Using DISTINCT to paper over duplicate rows from a join. Fix the join or use EXISTS. Otherwise you may be hiding a real data problem and paying for it in speed.
3. Forgetting the NULL row. One NULL shows up in the list even if hundreds of rows are blank. Filter it out if you do not want it.
4. Sorting by a column that is not selected. Most databases throw an error, SQLite silently gives an arbitrary order.
5. Using SUM(DISTINCT ...) for a total. It adds each different value once, so two $25 orders count as $25.
6. Thinking DISTINCT deletes duplicates. It only changes the result. Use a DELETE query to clean the table.

FAQ

What does DISTINCT do in SQL?

It removes duplicate rows from a query's result, so each unique value (or combination of values, if you select several columns) appears once. It does not change the data in the table.

How do I select distinct values from one column but show other columns?

You have to decide which of the other values to show. Use GROUP BY with an aggregate such as MAX or MIN, use DISTINCT ON in PostgreSQL, or use ROW_NUMBER() to pick one full row per value. See DISTINCT ON.

Does SELECT DISTINCT apply to all columns?

Yes. A row is removed only if every selected column matches another row. SELECT DISTINCT a, b returns unique pairs of a and b.

Is DISTINCT faster than GROUP BY?

For the same query they are usually equally fast, because most databases turn both into the same plan. Choose DISTINCT to list unique values and GROUP BY when you need aggregates per value.

Does DISTINCT include NULL values?

Yes. All NULLs are treated as equal, so SELECT DISTINCT returns one NULL row if the column has any NULLs. COUNT(DISTINCT col) ignores NULLs completely.

Can I use DISTINCT with COUNT?

Yes: COUNT(DISTINCT column) counts unique non-NULL values. See the COUNT DISTINCT guide.

Does DISTINCT sort the results?

Not reliably. Some databases sort as a side effect, others use hashing and return any order. Add ORDER BY if you need a specific order.

What is the difference between DISTINCT and UNIQUE in SQL?

DISTINCT removes duplicates from a query result. UNIQUE is a table constraint that prevents duplicate values from being inserted. Oracle also accepts SELECT UNIQUE as a synonym for SELECT DISTINCT.

Can I use DISTINCT on multiple columns?

Yes. List them after DISTINCT: SELECT DISTINCT customer, city FROM orders. You get each combination that exists once.

Practice

The free interactive DISTINCT lesson runs in your browser with no signup. It comes right after the SELECT and WHERE lessons, and the GROUP BY lesson is the natural next step. After that, the SQL practice problems include de-duplication questions with solutions, and "find the unique values" questions come up in most SQL interviews. Keep the SQL cheat sheet open for the syntax.