SQL SELECT DISTINCT

What does SQL SELECT DISTINCT do?

SELECT DISTINCT returns unique rows for the specified columns, eliminating duplicates from the result set.

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 SELECT DISTINCT: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and all SQL-92 compliant engines.

SQL SELECT DISTINCT Full Explanation

SELECT DISTINCT tells the database engine to scan the result produced by the SELECT list and discard rows that would be identical across all columns named in that list. It operates after the FROM, WHERE, JOIN, and GROUP BY clauses have filtered and shaped the data but before ORDER BY is applied. Two rows are considered duplicates when every value in the projected columns matches (NULLs are treated as equal to NULLs). Because the database must compare each row with previously seen rows, DISTINCT can increase memory use and execution time, especially on large, unsorted datasets. Indexes that cover the DISTINCT columns or a preceding GROUP BY on the same columns can mitigate this cost. DISTINCT does not accept arguments beyond the column list, and it affects only the columns explicitly selected, not hidden columns such as those in ORDER BY unless they also appear in the SELECT list. In PostgreSQL, the DISTINCT ON variant allows picking the first row of each distinct group, but standard SQL supports only the plain DISTINCT keyword.

SQL SELECT DISTINCT Syntax

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

SQL SELECT DISTINCT Parameters

column_list (list) - One or more columns or expressions to compare for uniqueness.
table_name (identifier) - Name of the source table or view.
condition (boolean) - Optional predicate that filters rows before DISTINCT is applied.

Example Queries Using SQL SELECT DISTINCT

-- 1. Unique list of countries
SELECT DISTINCT country
FROM customers;

-- 2. Unique combination of first and last names
SELECT DISTINCT first_name, last_name
FROM employees;

-- 3. Count distinct product IDs ordered this month
SELECT COUNT(DISTINCT product_id) AS unique_products
FROM orders
WHERE order_date >= DATE '2024-06-01';

Expected Output Using SQL SELECT DISTINCT

Each query returns only one row for every distinct combination of the specified columns
The third query returns a single integer representing the count of unique product_id values

Use Cases with SQL SELECT DISTINCT

Building dropdown menus that should not show duplicates (e.g., list of countries).
Deriving unique keys for further joins or analysis.
Counting how many distinct values exist in a column.
De-duplicating data for export or reporting when the table itself may contain repeats.

Common Mistakes with SQL SELECT DISTINCT

Assuming DISTINCT removes duplicates across entire rows when only a subset of columns is selected.
Using DISTINCT in place of GROUP BY when aggregate calculations are needed.
Expecting DISTINCT to sort results; ORDER BY is still required.
Applying DISTINCT on large datasets without supporting indexes, leading to slow queries.

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the main purpose of SELECT DISTINCT?

It removes duplicate rows from the query result, returning each unique value or combination only once.

Is SELECT DISTINCT case sensitive?

Case sensitivity depends on the database collation. In most default collations for MySQL and PostgreSQL it is case-insensitive, while binary collations treat case differently.

Can I use DISTINCT with aggregate functions?

Yes. You can write expressions like COUNT(DISTINCT column) to count unique values. However, you cannot place DISTINCT after an aggregate in the SELECT list.

How do I sort the results of a DISTINCT query?

Add an ORDER BY clause after the SELECT DISTINCT statement: `SELECT DISTINCT city FROM customers ORDER BY city;`