DocsFundamentalsDISTINCT

DISTINCT

Fundamentals

Concept

DISTINCT removes duplicate rows from the result set. When you SELECT DISTINCT, the database compares all selected columns across every row and collapses identical rows into one. It is a simple, powerful way to answer questions like "which categories exist?" or "which cities have customers?".

DISTINCT operates on the entire selected row, not just one column. If you SELECT DISTINCT city, state, the combination of city and state must be unique — the same city in two different states will produce two rows. For counting unique values within aggregations, use COUNT(DISTINCT column) instead.

Be mindful that DISTINCT requires a sort or hash operation to identify duplicates, which adds processing cost. If you find yourself applying DISTINCT to fix a query that returns too many rows, the root cause is often a missing or incorrect JOIN condition rather than a genuine need for deduplication.

Syntax

-- Unique values from one column
SELECT DISTINCT category FROM products;

-- Unique combinations
SELECT DISTINCT city, state FROM customers;

-- Count distinct values
SELECT COUNT(DISTINCT customer_id) AS unique_buyers
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Find all unique product categories in the catalog
SELECT DISTINCT category
FROM products
ORDER BY category;

-- How many distinct customers ordered each month?
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Common Pitfalls & Tips

1DISTINCT on many columns can be slow — the database must compare every column for every row pair. Consider whether GROUP BY achieves the same result more efficiently.
2If your DISTINCT query unexpectedly returns duplicates, check whether you are selecting columns with invisible differences like trailing spaces or different cases.
3DISTINCT and GROUP BY often produce the same output, but GROUP BY allows aggregate functions while DISTINCT does not.

Practice DISTINCT queries