Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL SELECT DISTINCT

Reference for SQL SELECT DISTINCT: remove duplicate rows, how it treats NULL and multiple columns, DISTINCT ON in PostgreSQL, and common mistakes.

SELECT DISTINCT removes duplicate rows from a result. Two rows are duplicates when every selected column has the same value. The database keeps one copy of each distinct row and discards the rest.

It applies to the whole row, not to the first column. SELECT DISTINCT city, country returns each distinct pair, not each distinct city.

SQL
SELECT DISTINCT country
FROM customers;

Try it

SQL Distinct values and distinct combinations ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL DISTINCT treats NULLs as equal ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
SELECT DISTINCT column [, column ...]
FROM table;

SELECT COUNT(DISTINCT column) FROM table;
Part Description
DISTINCT Placed directly after SELECT. Compares all select-list columns together.
ALL The opposite and the default: keep duplicates.
COUNT(DISTINCT x) Counts distinct non-NULL values of x. See COUNT().

Returns: the same columns as a plain SELECT, with duplicate rows removed.

Dialect differences

  • SQLite, MySQL, SQL Server, Oracle, PostgreSQL all support SELECT DISTINCT and COUNT(DISTINCT x).
  • PostgreSQL adds DISTINCT ON, which keeps the first row per key according to ORDER BY:
SQL
-- PostgreSQL only
SELECT DISTINCT ON (customer_id) customer_id, ordered_at, status
FROM orders
ORDER BY customer_id, ordered_at DESC;
  • Portable alternative for "latest row per group": a window function with ROW_NUMBER().
  • Multi-column COUNT(DISTINCT a, b) works in MySQL but not in standard SQL, SQLite or PostgreSQL. Count over a subquery of SELECT DISTINCT a, b instead.
  • Oracle also accepts the older synonym UNIQUE.

Usage notes

  • NULLs are treated as equal to each other by DISTINCT, so several NULLs collapse into one row. This differs from =, where NULL = NULL is not true.
  • It is not a function. SELECT DISTINCT(a), b still applies to both columns; the parentheses do nothing.
  • Cost: DISTINCT usually needs a sort or hash of the whole result. Do not add it "just in case"; a join that multiplies rows is better fixed at the join.
  • Do not use it to hide a bad join. If duplicates appear, find out why before removing them.
  • ORDER BY columns must appear in the select list when you use DISTINCT in standard SQL (PostgreSQL enforces this).
  • Set operators such as UNION also remove duplicates; UNION ALL does not.