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.
SELECT DISTINCT country FROM customers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 DISTINCTandCOUNT(DISTINCT x). - PostgreSQL adds
DISTINCT ON, which keeps the first row per key according toORDER BY:
-- 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 ofSELECT DISTINCT a, binstead. - 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=, whereNULL = NULLis not true. - It is not a function.
SELECT DISTINCT(a), bstill applies to both columns; the parentheses do nothing. - Cost:
DISTINCTusually 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 BYcolumns must appear in the select list when you useDISTINCTin standard SQL (PostgreSQL enforces this).- Set operators such as
UNIONalso remove duplicates;UNION ALLdoes not.