DISTINCT

Unique values only

In the vault’s great hall, every sound echoes. The same status appears across thousands of orders. The same category appears across hundreds of products. The same city appears in many user profiles.

When you need the full stream of data, repeated values are perfectly normal.

For example, when you look at orders, the status paid may appear many times:

SELECT id, status
FROM orders;

The result might look like this:

Here, every row represents a separate order. The repeated statuses are not a mistake; several orders simply share the same status.

But sometimes you do not need the full stream of orders—you need a concise list:

Which statuses appear in the table?

In that case, you do not need every occurrence of paid and pending. You want to see each value only once.

That is what DISTINCT is for:

SELECT DISTINCT status
FROM orders;

The result:

DISTINCT removes duplicate rows from a query’s result.

It does not change the data in the table.
It does not delete rows from the database.
It only removes duplicates from the output.

QUERY: The archive’s echo is not always noise. Sometimes it tells you how often something happens. But when you want a clean list rather than a chorus of repeats, turn on the silencer: DISTINCT.

Where DISTINCT goes

DISTINCT comes immediately after SELECT:

SELECT DISTINCT status
FROM orders;

That placement matters.

Not like this:

SELECT status DISTINCT
FROM orders;

And not like this:

SELECT status
FROM orders
DISTINCT;

The correct form is:

SELECT DISTINCT columns
FROM table;

For example, a list of product categories:

SELECT DISTINCT category
FROM products;

A list of the cities users come from:

SELECT DISTINCT city
FROM users;

A list of order statuses:

SELECT DISTINCT status
FROM orders;

The idea is the same in all of these queries:

Show me each value that appears, with no duplicates.

DISTINCT on a single column

The simplest case is selecting the unique values from one column.

Take the products table:

If you write a regular query:

SELECT category
FROM products;

the result shows the category from every row:

But if you need a list of categories without duplicates:

SELECT DISTINCT category
FROM products;

you get:

DISTINCT does not pick “the first product in a category.”
It does not group products for counting.
It simply answers this question:

What distinct values appear in this column?

DISTINCT on several columns

Here is the key detail of this lesson:

DISTINCT considers the entire selected row when deciding whether two rows are duplicates.

If you select one column, DISTINCT removes duplicate values from that column.

SELECT DISTINCT category
FROM products;

But if you select two columns:

SELECT DISTINCT category, status
FROM products;

then DISTINCT looks for unique pairs:

category + status

Suppose we have this table:

The query:

SELECT DISTINCT category
FROM products;

returns:

The query:

SELECT DISTINCT category, status
FROM products;

returns:

Why?

Because uniqueness is now determined by the pair, not by category alone:

category + status

The rows:

Books + active
Books + archived

are different as far as SQL is concerned, even though they have the same category.

So DISTINCT does not mean “make the first column unique.” It means:

Remove duplicate result rows, considering every selected column.

How DISTINCT treats NULL

NULL represents a missing value, but for DISTINCT, rows with NULL in the same column are treated as duplicates.

Take the users table:

The query:

SELECT DISTINCT city
FROM users;

returns approximately this set:

The two rows with NULL do not appear twice. The result contains a single row with NULL.

That does not contradict the previous lesson about NULL.

In a WHERE condition, using = to compare a value with NULL produces UNKNOWN. But DISTINCT serves a different purpose: it removes duplicates from the completed output. During deduplication, several missing values in one column collapse into a single row.

If you would rather not see NULL in the list, add a filter:

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

Then the result shows only cities with non-null values.

DISTINCT does not sort the result

DISTINCT removes duplicates, but it does not determine the order of the rows.

For example:

SELECT DISTINCT status
FROM orders;

may return:

The order may be different the next time.

That is normal: SQL does not guarantee a “natural” row order unless you explicitly request a sort.

If you want a tidy list, add ORDER BY:

SELECT DISTINCT status
FROM orders
ORDER BY status;

Now the query gives two separate instructions:

SELECT DISTINCT status

— remove the duplicates;

ORDER BY status

— sort the result by status.

Don’t expect DISTINCT to sort the result for you. It silences duplicates; it does not sort.

DISTINCT vs GROUP BY

For a simple list of values, these two queries can produce the same result:

SELECT DISTINCT category
FROM products;

and:

SELECT category
FROM products
GROUP BY category;

Both return a list of categories with no duplicates.

But they mean different things.

DISTINCT answers the question:

Which distinct values are present?

GROUP BY answers the question:

How should the rows be grouped so that something can be calculated for each group?

For example, if all you need is a list of categories, DISTINCT is enough:

SELECT DISTINCT category
FROM products;

If you need to know how many products are in each category, use GROUP BY:

SELECT category, COUNT(*) AS products_count
FROM products
GROUP BY category;

The result might look like this:

Here GROUP BY gathers rows into groups, allowing the aggregate function COUNT(*) to count the rows in each group.

So the practical rule is this:

If you need a list of distinct values, use DISTINCT.

SELECT DISTINCT category
FROM products;

If you need to count something for each group, use GROUP BY.

SELECT category, COUNT(*)
FROM products
GROUP BY category;

The price of silence

DISTINCT may be a short keyword, but it gives the database real work to do.

To remove duplicates, the database has to determine which result rows are identical. That requires comparing rows. Depending on the situation, the database may:

• sort the result and remove adjacent duplicates;
• build a hash table of unique values;
• take advantage of a suitable index, if one is available.

On a small table, the extra work may be barely noticeable.

For example, if products holds only 20 rows, the query:

SELECT DISTINCT category
FROM products;

will be fast.

But if the table holds millions of rows and many columns are selected, deduplication can account for a significant part of the query's cost.

A query like this one can be especially expensive:

SELECT DISTINCT *
FROM orders;

Here the database has to compare entire rows across every selected column. The more rows and columns there are, the more work the database has to do.

That is why you shouldn't add DISTINCT "just in case".

A good question to ask before you use it is:

Which duplicates do I want to remove, and why are they there?

If the answer is:

I need a list of statuses.

then DISTINCT is the right tool.

SELECT DISTINCT status
FROM orders;

If the answer is:

After joining some tables, I saw unexpected duplicates, so I added DISTINCT.

that's a warning sign. The problem may lie in the join logic, not in the output.

DISTINCT after JOIN: when it masks a problem

One of the riskiest uses of DISTINCT is adding it to "fix" duplicates after a JOIN.

We haven't looked at joins in detail yet, but the idea is worth remembering now.

Suppose we have users and orders.

One user can place many orders.

If you join users to orders, a user's details can appear multiple times—once for each order.

For example:

If all you need is a list of users, you can end up seeing Anna three times.

In a situation like this, you might write:

SELECT DISTINCT users.id, users.name
FROM users
JOIN orders ON orders.user_id = users.id;

The result now looks tidy:

But remember: DISTINCT did not explain why Anna appeared three times. It simply removed the duplicate rows after the join.

That may be exactly what you want if you need a list of users who have placed at least one order.

But adding DISTINCT simply because "I get duplicates without it, and I don't know why" is a warning sign.

The right approach is to understand the relationships in your data:

• whether the relationship between the tables is one-to-one or one-to-many;
• why one row in the left table matches several rows in the right table;
• whether you really need a list of unique users;
• whether you need to count their orders instead;
• whether you need to select one specific order;
• whether the join condition needs to change.

DISTINCT after a JOIN can be perfectly valid, but it shouldn't be used as a patch for a problem you don't understand.

QUERY: If the echo started after you opened the door to the next hall, don't rush to silence the whole archive. First check which door you opened.

PostgreSQL bonus: DISTINCT ON

PostgreSQL offers a special construct:

SELECT DISTINCT ON (expression) ...

It works differently from ordinary DISTINCT.

Ordinary DISTINCT removes result rows that are completely identical.

For example:

SELECT DISTINCT category, status
FROM products;

This returns each unique combination of category + status.

DISTINCT ON, on the other hand, says:

Keep only one row for each value of this expression.

For example, suppose you need to pick one product from each category:

SELECT DISTINCT ON (category)
       category, name, price
FROM products
ORDER BY category, price DESC;

Here's how to read this query:

1. separate the rows by category;
2. within each category, sort the products from highest to lowest price;
3. keep the first row in each category.

The result might look like this:

Here DISTINCT ON (category) keeps one row per category, and ORDER BY category, price DESC decides which row exactly ends up first within a category.

Important:

DISTINCT ON is a PostgreSQL extension. Standard SQL doesn't have it, and neither do many other database systems.

At this stage, you don't need to memorise it or treat it as an essential tool. The important thing is to understand the difference:

• DISTINCT removes duplicate rows from the result;
• DISTINCT ON in PostgreSQL keeps the first row from each group defined by the expression you specify.

QUERY: Use DISTINCT when you mean to silence the echo. If you don't know where the echo came from, find the source first.