SQL DISTINCT

What does SQL DISTINCT do?

SQL DISTINCT removes duplicate rows from the result set, returning only unique combinations of the selected columns.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DISTINCT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery

SQL DISTINCT Full Explanation

DISTINCT is placed immediately after SELECT to filter out duplicate rows produced by the query’s FROM, JOIN, and WHERE clauses. The database engine evaluates the result set, compares the values of all selected columns, and returns only one instance of each unique combination. If multiple columns are listed, uniqueness is determined across the entire tuple, not per column. DISTINCT can be combined with aggregate functions such as COUNT to obtain counts of unique values. In PostgreSQL, the DISTINCT ON variation lets you specify leading columns for finer control, but it is non-standard. Because DISTINCT triggers a sort or hash aggregate internally, it can increase query execution time on large datasets. Adding selective WHERE clauses, proper indexing, or using GROUP BY can mitigate performance costs. Beware that DISTINCT applies after projections, so expressions or aliases affect uniqueness.

SQL DISTINCT Syntax

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

SQL DISTINCT Parameters

Example Queries Using SQL DISTINCT

-- Basic distinct list
SELECT DISTINCT country
FROM customers;

-- Multiple columns for composite uniqueness
SELECT DISTINCT first_name, last_name
FROM employees;

-- Counting unique values
SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;

-- PostgreSQL specific: DISTINCT ON
SELECT DISTINCT ON (customer_id) customer_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC;

Expected Output Using SQL DISTINCT

Each query returns only unique rows for the specified columns
Duplicate combinations are omitted
COUNT(DISTINCT
) returns a single integer representing the number of unique values

Use Cases with SQL DISTINCT

Produce de-duplicated lookup lists for UI dropdowns
Calculate counts of unique attribute values
Remove accidental duplicates in ad-hoc reporting
Feed unique keys into further joins or subqueries

Common Mistakes with SQL DISTINCT

Assuming DISTINCT operates per column rather than per full row
Using DISTINCT to mask bad joins instead of fixing the underlying query logic
Forgetting that ORDER BY columns must appear in SELECT when DISTINCT ON is used (PostgreSQL)
Expecting DISTINCT to improve performance; it usually adds overhead

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between DISTINCT and UNIQUE constraints?

A UNIQUE constraint enforces uniqueness at the table storage level, preventing duplicate data from being inserted. DISTINCT operates at query time, filtering duplicates in the result set without altering stored data.

Why does my DISTINCT query still return duplicates?

Check for hidden differences such as whitespace, letter casing, or additional selected columns. DISTINCT compares exact byte values of every selected column, so even minor variations create distinct rows.

Can I use DISTINCT inside aggregate functions?

Yes. For example, COUNT(DISTINCT column_name) counts the number of unique non-NULL values in that column.

When should I avoid DISTINCT?

Avoid using DISTINCT to fix bad joins or poor data modeling. Optimize your joins and filters first; use DISTINCT only when legitimate duplicates exist in the intended result.