SQL SELECT DISTINCT Statement Explained with Examples

A query comes back with the same value printed three times, once for each row that carries it. SELECT DISTINCT removes the repeats: put the keyword straight after SELECT, and the database keeps one row out of every group of rows whose selected columns are all equal. That word all is where the surprises come from, because DISTINCT weighs the whole select list rather than the first column in it.

What SELECT DISTINCT does

Two of the three rows in this table hold the same name:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name      VARCHAR(20)
);

INSERT INTO Students VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Alice');

A plain SELECT returns one output row per table row, repeats included:

SELECT Name FROM Students;

Adding one keyword keeps one of the two Alice rows and drops the other:

SELECT DISTINCT Name FROM Students;

The PostgreSQL 17 manual states the rule as "all duplicate rows are removed from the result set (one row is kept from each group of duplicates)". DISTINCT goes directly after SELECT, once per query, and it covers everything in the select list. DISTINCT is not a function, so it takes no column of its own and there is no second place in the statement to put it.

Plain SELECT against SELECT DISTINCT

The two queries above differ by one keyword and by one row, and the choice between them is about where the repeats came from. A column that genuinely holds the same value on several rows, such as Name above, is what DISTINCT is for. Repeats that appeared only after you joined a second table are the join's doing. DISTINCT hides them without changing the join that produces them on every run, so read the join condition first.

Removing duplicates says nothing about arranging what is left, so a DISTINCT result comes back in no particular order until the query asks for one with ORDER BY. Two runs of the same DISTINCT query can list the same rows in a different sequence, which is worth knowing before you paste one into a report that people read top to bottom.

SQL DISTINCT on multiple columns

Name a second column and DISTINCT starts comparing pairs, so the result holds every combination that occurs at least once:

CREATE TABLE Orders (
    OrderID  INT PRIMARY KEY,
    Product  VARCHAR(20),
    Color    VARCHAR(20),
    Quantity INT
);

INSERT INTO Orders VALUES
    (1, 'Apple',  'Red',    2),
    (2, 'Apple',  'Green',  4),
    (3, 'Orange', 'Orange', 1),
    (4, 'Apple',  'Red',    3);

Orders 1 and 4 are the only pair that match on both of the columns below, so they are the only pair that can collapse:

SELECT DISTINCT Product, Color FROM Orders;

Apple comes back twice, once per color it was ordered in, because Apple with Red and Apple with Green are two different pairs. Asking for the products alone gives the shorter answer:

SELECT DISTINCT Product FROM Orders;

SQL DISTINCT and NULL values

A comparison against NULL is normally neither true nor false, which would leave DISTINCT unable to decide whether two empty cells are duplicates. The engines settle it by exception. MySQL's manual puts it as "When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal", and three employees are enough to see what that produces:

CREATE TABLE Employees (
    ID      INT PRIMARY KEY,
    Name    VARCHAR(20),
    Address VARCHAR(40)
);

INSERT INTO Employees VALUES
    (1, 'John',    NULL),
    (2, 'Michael', '123 Lane St'),
    (3, 'Sarah',   NULL);

SELECT DISTINCT Address FROM Employees;

Two employees have no address on file and the result shows one empty row, which is the one place where NULL behaves like an ordinary value. Everywhere else it does not, and SQL NULL values covers what that costs a filter or a join.

SQL DISTINCT vs GROUP BY

Both statements return one row per combination of the columns you name, and over the orders above they return the same three rows:

SELECT Product, Color FROM Orders GROUP BY Product, Color;

What GROUP BY has that DISTINCT does not is the group itself, which an aggregate function can then read. Orders 1 and 4 are two rows behind the Apple in Red line, and only GROUP BY can add up what they hold:

SELECT Product, Color, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product, Color;

Write DISTINCT when the list of combinations is the whole answer, because it says that in one word and no reader has to check whether an aggregate is hiding further down the query. Write GROUP BY as soon as you need a count, a sum, or an average per combination, and as soon as you need HAVING to filter on that number.

SELECT COUNT(DISTINCT column)

DISTINCT also goes inside COUNT, where it counts the different values instead of listing them:

SELECT COUNT(DISTINCT Name) AS DifferentNames FROM Students;

Three student rows, two names. The count and the list disagree about NULL, though, and the addresses show it: COUNT(DISTINCT expr) "returns a count of the number of rows with different non-NULL expr values", so the empty addresses that made one row above are counted zero times here.

SELECT COUNT(DISTINCT Address) AS DifferentAddresses FROM Employees;

One column, two answers: SELECT DISTINCT Address returns two rows, and COUNT(DISTINCT Address) returns 1. Put COUNT(*) beside it whenever the report has to separate the rows from the values that are actually on file.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Compare the raw rows and the DISTINCT result in DbSchema

Before a DISTINCT goes into reporting logic, it is worth seeing which rows it took out. Open a table in DbSchema's Relational Data Editor and the rows arrive in a grid under the diagram, in the state the database holds them.

Then paste the query into the DbSchema SQL Editor, click Execute Query, and read the result grid under the statement. Running the query with and without the keyword, and comparing the two row counts, tells you how many rows the duplicates account for. Neither step changes a row, since both queries only read. DbSchema keeps the editors themselves in the model file, so each one reopens with the model.

Common mistakes

The first mistake is writing DISTINCT as though it were a function of one column. The parentheses are accepted and they change nothing, because DISTINCT still applies to the whole select list:

SELECT DISTINCT(Product), Color FROM Orders;

Three rows, exactly as before: Color is still part of the comparison, however the query is punctuated. The second mistake follows from the same rule, and it is the one that makes DISTINCT look broken. Select a column whose values are already unique and every row becomes its own group:

SELECT DISTINCT OrderID, Product, Color FROM Orders;

The two Apple in Red orders are back, because their OrderID values differ and DISTINCT compares whole rows. A primary key in the select list makes DISTINCT a no-op, so take out the columns you are not grouping by. The third mistake is reading an empty cell in a DISTINCT result as one missing value, when it stands for every row whose column was empty.

Practice questions

1. List the colors ordered at least once, with no color repeated.
2. Count the different products in the Orders table.
3. List the product and color combinations of the Apple orders only.
4. Return the same combinations as question 3, with the number of items ordered for each.

The difference is easiest to believe on your own tables. Download DbSchema at https://dbschema.com/download.html, connect, and run one of your queries with and without DISTINCT in the SQL Editor to see how many rows separate the two. The SQL Editor and the connection are in the free Community Edition, and the Relational Data Editor that shows the raw rows beside it is in Pro.

Frequently asked questions

Can I combine DISTINCT with other SQL functions?

DISTINCT goes inside an aggregate, as COUNT(DISTINCT Name) does above. SQL Server's AVG takes it too, where DISTINCT "specifies that AVG operates only on one unique instance of each value", so AVG(DISTINCT Quantity) averages each different quantity once. DISTINCT is not a function itself, so it cannot be aimed at one column of a select list that names several.