DISTINCT
DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
Overview
DISTINCT is a SELECT modifier that eliminates duplicate rows from the result set. Two rows are considered duplicates when every selected column has the same value in both. It's commonly used to answer questions like "what are the unique countries in this table?" or "how many distinct customers placed an order this month?"
Copy code
SELECT DISTINCT country
FROM customers;
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;
DISTINCT applies to the entire row of selected columns, not just the first one -- SELECT DISTINCT country, city deduplicates on the combination of both columns together.
DISTINCT ON in DuckDB
DuckDB (like PostgreSQL) supports DISTINCT ON, which returns one row per unique value of a specified expression, rather than requiring the whole row to be unique. Combined with ORDER BY, it's a concise way to pick "the top row per group" in a single query:
Copy code
SELECT DISTINCT ON (country) country, city, population
FROM cities
ORDER BY country, population DESC;
This returns the most populous city for each country, without a window function or subquery. Without an ORDER BY, the row returned for each group is unspecified.
Performance considerations
DISTINCT requires comparing (and typically sorting or hashing) every row, which can be expensive on large tables. When you only need uniqueness on a subset of columns, DISTINCT ON or a GROUP BY is often cheaper than deduplicating the full row.
DISTINCT vs GROUP BY
SELECT DISTINCT col FROM t and SELECT col FROM t GROUP BY col return equivalent results when no aggregate functions are involved; GROUP BY is generally preferred when you also need to compute aggregates alongside the grouping column.
Related terms
HyperLogLog is a probabilistic algorithm for estimating the number of distinct elements in a large dataset (cardinality) using a small, fixed amount of memory, at the cost of a small, predictable error margin.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
ROW_NUMBER →ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.
Deduplication →Deduplication is the process of identifying and removing duplicate records from a dataset, keeping only one representative row per logical entity.
GROUP BY clause →GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.
Cardinality →Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).
FAQS
Yes -- DISTINCT applies to the full combination of selected columns, so SELECT DISTINCT col1, col2 keeps only unique (col1, col2) pairs, not unique values within each column separately.
DISTINCT ON (available in DuckDB and PostgreSQL) deduplicates based on a subset of columns/expressions while still returning other columns from one representative row per group, typically controlled with ORDER BY -- plain DISTINCT requires the entire selected row to be unique.
It can be, since DuckDB must track every unique value seen; for approximate cardinality on very large datasets, approx_count_distinct() trades a small amount of accuracy for significantly better performance.
