SQL guide · Querying

SQL SELECT DISTINCT: Return Only Unique Values

The DISTINCT keyword removes duplicate rows from a query result. You place it directly after SELECT, and the database returns each unique combination of the selected columns once. It is the quickest way to answer questions such as which countries appear in a customer table, or which product categories have been ordered.

It is important to understand that DISTINCT applies to the whole row of selected values, not to the first column only. SELECT DISTINCT country, city returns each unique country and city pair, so a country can still appear many times if it has many cities. Two rows are considered duplicates only when every selected column is equal. For this comparison, NULL values are treated as equal to each other, so a column full of nulls collapses to a single null row.

Behind the scenes the database usually sorts or hashes the result to find duplicates, which costs time on large tables. If you only need to know how many different values exist, use COUNT(DISTINCT column). If you need duplicates removed because a join multiplied your rows, consider whether the join is correct before reaching for DISTINCT, since it can hide a data modelling problem. GROUP BY produces the same result as DISTINCT for simple cases and also lets you aggregate.

Basic syntax

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

SELECT COUNT(DISTINCT column1)
FROM table_name;

DISTINCT is a modifier of the whole select list, so you cannot apply it to only one column in a multi-column list. Inside an aggregate, COUNT(DISTINCT col) counts unique non-null values of that single expression.

SQL SELECT DISTINCT examples

Unique values in one column

This lists every country that appears in the customers table once, however many customers live there. Adding ORDER BY makes the list easy to scan.

SELECT DISTINCT country
FROM customers
ORDER BY country;

Unique combinations of columns

With two columns the uniqueness test uses the pair. The result has one row for each distinct country and city combination found in the table.

SELECT DISTINCT country, city
FROM customers;

Count distinct values

Wrap a column in COUNT(DISTINCT ...) to count how many different values it contains. This returns the number of customers who placed at least one order, not the number of orders.

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM orders;

Distinct after a join

A join can repeat a customer once per matching order. Selecting only the customer columns and using DISTINCT returns each customer once.

SELECT DISTINCT c.customer_id, c.first_name, c.email
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

Where SQL dialects differ

PostgreSQL: Adds DISTINCT ON (expr), which keeps the first row of each group according to ORDER BY. For example SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC returns each customer latest order.

MySQL: Supports DISTINCT and COUNT(DISTINCT a, b) with multiple expressions. Unlike some databases it can use an index to avoid a sort when the distinct columns form an index prefix. DISTINCTROW is a synonym.

SQL Server: Supports SELECT DISTINCT TOP (n). COUNT(DISTINCT ...) accepts only one expression. Columns of type text, ntext and image cannot be used with DISTINCT.

SQLite: Supports DISTINCT and COUNT(DISTINCT col) with a single argument. Text comparison is case sensitive by default, so Paris and paris count as different values unless you use COLLATE NOCASE.

Oracle: Supports DISTINCT and the synonym UNIQUE. It cannot be applied to LOB columns. Note that Oracle treats an empty string as NULL, which affects how blanks collapse.

Common mistakes

  • Expecting DISTINCT to apply to a single column in a multi-column select. It always compares the whole row of selected values.
  • Using DISTINCT to hide duplicates caused by a wrong join condition. Fix the join first, otherwise totals calculated later will be inflated.
  • Putting parentheses around one column, as in SELECT DISTINCT(country), city. The parentheses do nothing, and DISTINCT still covers both columns.
  • Ordering by a column that is not in the select list when DISTINCT is used. Most databases reject this because the sort key is ambiguous.
  • Assuming COUNT(DISTINCT col) counts nulls. Null values are ignored, so add one yourself if you need them counted.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator