SQL 24 🛢️ Removing Duplicates with DISTINCT

A query returns rows. Sometimes those rows contain duplicates. The DISTINCT clause removes them. It tells the database to return only the unique combinations of the selected columns. If two rows have the same values in every selected column, only one is returned. The DISTINCT clause is the tool for answering questions like “Which cities do our customers live in?” or “What are the unique order statuses?” without returning the same value multiple times.

The previous chapters covered filtering, sorting, and limiting. This chapter covers deduplication. DISTINCT is part of the SELECT clause, and it affects every column in the select list. It is often confused with GROUP BY, which also produces unique combinations but for a different purpose. Understanding the difference is the key to using both correctly.

Key point: DISTINCT applies to the entire row of the result set, not to a single column. The expression SELECT DISTINCT city, state FROM customers returns unique combinations of city and state, not unique cities. If two rows have the same city but different states, both appear in the result. To deduplicate a single column, select only that column, or use GROUP BY.

---

Why DISTINCT Matters

A table can contain duplicate values in a column without violating any constraint. A customers table can have a hundred customers in New York. The city column contains 'New York' a hundred times. A query that lists the cities should return each city once, not a hundred times. The DISTINCT clause produces that result.

The reporting problem. A report that lists the distinct product categories should not repeat each category for every product. The DISTINCT clause produces the list.

The validation problem. A query that checks whether a column contains only expected values should return the distinct values. The DISTINCT clause makes the check easy: the result is short and readable.

The aggregation problem. The COUNT(DISTINCT column) expression counts the number of unique values in a column. It is the natural companion to the DISTINCT clause.

The join problem. A join can produce duplicate rows when the join condition matches multiple rows in the right table. The DISTINCT clause removes the duplicates. But it is often a symptom of a missing GROUP BY or an incorrect join condition.

The trade-off. The DISTINCT clause has a cost. The database must sort the result set or use a hash table to find the duplicates. For a large result set, the cost is significant. The DISTINCT clause should be used when duplicates are expected, not as a reflex to hide a problem in the query.

---

a. The DISTINCT Clause

The DISTINCT clause comes immediately after the SELECT keyword. It applies to all the columns in the select list.

SELECT DISTINCT city FROM customers;
SELECT DISTINCT status FROM orders;
SELECT DISTINCT department FROM employees;

The first query returns each city once. The second returns each order status once. The third returns each department once.

The DISTINCT clause can be combined with multiple columns. The result contains unique combinations of the selected columns.

SELECT DISTINCT city, state FROM customers;

The query returns each unique combination of city and state. If two customers live in 'New York', 'NY', the combination appears once. If another customer lives in 'New York', 'NY' but a third lives in 'New York', 'NY' — the same combination — it appears once. A customer in 'New York', 'NY' and a customer in 'New York', 'NY' produce the same combination. A customer in 'New York', 'NY' and a customer in 'New York', 'NY' are the same combination.

The DISTINCT clause can be combined with ORDER BY and LIMIT.

SELECT DISTINCT city FROM customers ORDER BY city LIMIT 10;

The query returns the first ten distinct cities in alphabetical order.

The DISTINCT clause can be combined with aggregate functions. The COUNT(DISTINCT column) expression counts the number of unique values.

SELECT COUNT(DISTINCT city) FROM customers;
SELECT COUNT(DISTINCT customer_id) FROM orders;

The first query counts the number of unique cities. The second counts the number of unique customers who have placed orders.

---

b. DISTINCT vs GROUP BY

The DISTINCT clause and the GROUP BY clause both produce unique combinations of values. They differ in purpose and in what else they can do.

The DISTINCT clause is a row-level operation. It removes duplicate rows from the result set. The GROUP BY clause is an aggregation operation. It groups rows by the specified columns and applies aggregate functions to each group.

-- DISTINCT: unique cities
SELECT DISTINCT city FROM customers;

-- GROUP BY: unique cities with a count
SELECT city, COUNT(*) FROM customers GROUP BY city;

The DISTINCT query returns one row per city. The GROUP BY query returns one row per city plus the count of customers in that city.

The GROUP BY clause can do everything the DISTINCT clause can do, plus aggregation. The DISTINCT clause is a shorthand for the GROUP BY when no aggregation is needed.

-- These two queries return the same rows
SELECT DISTINCT city FROM customers;
SELECT city FROM customers GROUP BY city;

The two queries are logically equivalent. The database may optimize them differently. The DISTINCT clause is clearer when the intent is deduplication. The GROUP BY clause is necessary when the intent is aggregation.

The DISTINCT clause can be combined with GROUP BY. The GROUP BY groups the rows, and the DISTINCT removes duplicates from the grouped result. The combination is rarely needed and can usually be simplified.

Aspect	DISTINCT	GROUP BY
Purpose	Remove duplicate rows	Aggregate rows
Aggregation	No	Yes
HAVING	Not allowed	Allowed
Combination	Applied after grouping	Applied before distinct
Use case	Unique values	Counts, sums, averages

---

c. DISTINCT ON and Other Variants

PostgreSQL provides the DISTINCT ON clause, which is a PostgreSQL-specific extension. It returns the first row for each unique combination of the specified columns.

SELECT DISTINCT ON (customer_id) customer_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;

The query returns one row per customer — the most recent order for each customer. The DISTINCT ON (customer_id) clause groups the rows by customer_id. The ORDER BY clause determines which row is the first in each group. The result is the latest order for each customer.

The DISTINCT ON clause is powerful but non-standard. It is supported only by PostgreSQL. The same result can be achieved in other databases with a window function or a correlated subquery.

-- The portable alternative using ROW_NUMBER()
SELECT * FROM (
    SELECT
        customer_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) AS numbered
WHERE rn = 1;

The query assigns a row number to each order within each customer, ordered by date descending. The outer query selects only the rows where the row number is 1 — the most recent order for each customer. The approach works in PostgreSQL, MySQL, SQL Server, and Oracle.

The DISTINCT clause can also be used with the COUNT function to count unique values.

SELECT COUNT(DISTINCT customer_id) FROM orders;
SELECT COUNT(DISTINCT city) FROM customers;

The COUNT(DISTINCT column) expression counts the number of unique non-null values in the column. The NULL values are ignored.

Variant	Database	Purpose
DISTINCT	All	Remove duplicate rows
DISTINCT ON	PostgreSQL	Return the first row per group
COUNT(DISTINCT col)	All	Count unique values
ROW_NUMBER()	All	Portable alternative to DISTINCT ON

---

Complete Example Session

This session demonstrates the DISTINCT clause on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    city        VARCHAR(50),
    state       VARCHAR(2),
    country     VARCHAR(50)
);

INSERT INTO customers VALUES
    (1, 'Alice', 'Johnson', 'New York', 'NY', 'USA'),
    (2, 'Bob', 'Smith', 'Chicago', 'IL', 'USA'),
    (3, 'Carol', 'Williams', 'New York', 'NY', 'USA'),
    (4, 'Dave', 'Brown', 'New York', 'NY', 'USA'),
    (5, 'Eve', 'Davis', 'Boston', 'MA', 'USA'),
    (6, 'Frank', 'Miller', 'Chicago', 'IL', 'USA'),
    (7, 'Grace', 'Wilson', 'New York', 'NY', 'USA'),
    (8, 'Henry', 'Moore', NULL, NULL, 'USA');

-- ============================================
-- PART 2: DISTINCT ON ONE COLUMN
-- ============================================

SELECT DISTINCT city FROM customers;

-- Output:
-- New York
-- Chicago
-- Boston
-- NULL

-- Four distinct values, including NULL.
-- New York appears once even though five customers live there.

-- ============================================
-- PART 3: DISTINCT ON MULTIPLE COLUMNS
-- ============================================

SELECT DISTINCT city, state FROM customers;

-- Output:
-- New York | NY
-- Chicago  | IL
-- Boston   | MA
-- NULL     | NULL

-- The distinct combinations of city and state.

-- ============================================
-- PART 4: DISTINCT WITH ORDER BY
-- ============================================

SELECT DISTINCT city FROM customers ORDER BY city;

-- Output:
-- Boston
-- Chicago
-- New York
-- NULL

-- The NULL is included and sorted according to the database's rules.

-- ============================================
-- PART 5: COUNT(DISTINCT)
-- ============================================

SELECT COUNT(DISTINCT city) FROM customers;

-- Output: 3
-- Three distinct non-null cities.
-- The NULL city is ignored by COUNT(DISTINCT).

SELECT COUNT(DISTINCT state) FROM customers;

-- Output: 3
-- Three distinct non-null states.

-- ============================================
-- PART 6: DISTINCT VS GROUP BY
-- ============================================

-- These two queries return the same rows:
SELECT DISTINCT city FROM customers;
SELECT city FROM customers GROUP BY city;

-- The GROUP BY version can also count:
SELECT city, COUNT(*) FROM customers GROUP BY city;

-- Output:
-- New York | 5
-- Chicago  | 2
-- Boston   | 1
-- NULL     | 1

-- ============================================
-- PART 7: DISTINCT ON (POSTGRESQL)
-- ============================================

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    total       DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders VALUES
    (1001, 1, '2026-01-15', 99.99),
    (1002, 1, '2026-06-20', 149.99),
    (1003, 2, '2026-03-10', 49.99),
    (1004, 3, '2026-04-05', 299.99),
    (1005, 1, '2026-09-12', 199.99);

SELECT DISTINCT ON (customer_id) customer_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;

-- Output:
-- 1 | 2026-09-12 | 199.99
-- 2 | 2026-03-10 | 49.99
-- 3 | 2026-04-05 | 299.99

-- One row per customer, the most recent order for each.

-- ============================================
-- PART 8: THE PORTABLE ALTERNATIVE
-- ============================================

SELECT * FROM (
    SELECT
        customer_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) AS numbered
WHERE rn = 1;

-- Output: the same three rows as DISTINCT ON.

-- The ROW_NUMBER() approach works in every database.

-- ============================================
-- PART 9: THE DISTINCT TRAP
-- ============================================

SELECT DISTINCT city, customer_id FROM customers;

-- Output: every row, because customer_id is unique.
-- The DISTINCT applies to the combination of city and customer_id.
-- Since customer_id is unique, the combination is unique.

-- The DISTINCT clause is not a fix for a missing WHERE clause.
-- It removes duplicates, not irrelevant rows.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- DISTINCT removes duplicate rows.
-- It applies to all selected columns.
-- COUNT(DISTINCT col) counts unique values.
-- DISTINCT ON returns the first row per group (PostgreSQL).
-- ROW_NUMBER() is the portable alternative.
-- DISTINCT is not a substitute for correct filtering.

The ten parts cover the table, DISTINCT on one column, DISTINCT on multiple columns, DISTINCT with ORDER BY, COUNT(DISTINCT), DISTINCT vs GROUP BY, DISTINCT ON, the portable alternative, the DISTINCT trap, and the summary.

---

Quick Reference

The DISTINCT Syntax

Syntax	Purpose
SELECT DISTINCT col	Unique values of the column
SELECT DISTINCT col1, col2	Unique combinations
COUNT(DISTINCT col)	Count unique values
SELECT DISTINCT ON (col) ...	First row per group (PostgreSQL)

The DISTINCT vs GROUP BY

Aspect	DISTINCT	GROUP BY
Purpose	Remove duplicates	Aggregate
Aggregation	No	Yes
HAVING	Not allowed	Allowed
Use case	Unique values	Counts, sums

The DISTINCT Variants

Variant	Database	Purpose
DISTINCT	All	Remove duplicates
DISTINCT ON	PostgreSQL	First row per group
ROW_NUMBER()	All	Portable alternative

The DISTINCT and NULL

Expression	Result
DISTINCT with NULL	NULL is a distinct value
COUNT(DISTINCT col)	NULL is ignored
DISTINCT with multiple columns	NULL in any column is distinct

---

Best Practices

✅ Do This:

-- Use DISTINCT for unique values
SELECT DISTINCT city FROM customers                            -- ✅

-- Use COUNT(DISTINCT) for unique counts
SELECT COUNT(DISTINCT city) FROM customers                     -- ✅

-- Use GROUP BY when you need aggregation
SELECT city, COUNT(*) FROM customers GROUP BY city             -- ✅

-- Use ROW_NUMBER() for the portable first-per-group
SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM t) sub WHERE rn = 1 -- ✅

❌ Don’t Do This:

-- Don't use DISTINCT to hide a bad join
SELECT DISTINCT * FROM orders o JOIN customers c ON ...        -- ⚠️

-- Don't assume DISTINCT applies to one column
SELECT DISTINCT city, customer_id FROM customers               -- ⚠️

-- Don't use DISTINCT when GROUP BY is needed
SELECT DISTINCT city, COUNT(*) FROM customers                  -- ❌

-- Don't use DISTINCT as a substitute for WHERE
SELECT DISTINCT * FROM customers                               -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
DISTINCT returns all rows	The column combination is unique	Select only the columns to deduplicate
COUNT(DISTINCT) off by one	NULL values ignored	Use COUNT(*) or COALESCE
DISTINCT slow	Large result set	Use GROUP BY or add an index
DISTINCT hides a bad join	Incorrect join condition	Fix the join, not the symptom

---

Real-World Examples

1. Unique Cities

SELECT DISTINCT city FROM customers;

2. Unique City-State Pairs

SELECT DISTINCT city, state FROM customers;

3. Count Unique Values

SELECT COUNT(DISTINCT customer_id) FROM orders;

4. Unique with Order

SELECT DISTINCT city FROM customers ORDER BY city;

5. Unique with Limit

SELECT DISTINCT city FROM customers ORDER BY city LIMIT 10;

6. DISTINCT ON (PostgreSQL)

SELECT DISTINCT ON (customer_id) customer_id, order_date, total FROM orders ORDER BY customer_id, order_date DESC;

7. ROW_NUMBER Alternative

SELECT * FROM (SELECT customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) sub WHERE rn = 1;

8. DISTINCT vs GROUP BY

SELECT city, COUNT(*) FROM customers GROUP BY city;

9. Unique Statuses

SELECT DISTINCT status FROM orders;

10. Unique Departments

SELECT DISTINCT department FROM employees;

---

Visual

The DISTINCT Clause

┌──────────────────────────────────────────────┐
│  DISTINCT                                    │
│                                              │
│  Data:                                       │
│    New York, Chicago, New York, Boston,      │
│    New York, Chicago, New York               │
│                                              │
│  SELECT DISTINCT city FROM customers;        │
│    → New York, Chicago, Boston               │
│                                              │
│  Removes duplicate rows from the result.     │
│                                              │
└──────────────────────────────────────────────┘

DISTINCT vs GROUP BY

┌──────────────────────────────────────────────┐
│  DISTINCT                                    │
│    SELECT DISTINCT city FROM customers;      │
│    → New York                                │
│    → Chicago                                 │
│    → Boston                                  │
│                                              │
│  GROUP BY                                    │
│    SELECT city, COUNT(*) FROM customers      │
│    GROUP BY city;                            │
│    → New York | 5                            │
│    → Chicago  | 2                            │
│    → Boston   | 1                            │
│                                              │
│  DISTINCT deduplicates.                      │
│  GROUP BY aggregates.                        │
│                                              │
└──────────────────────────────────────────────┘

The DISTINCT ON Pattern

┌──────────────────────────────────────────────┐
│  DISTINCT ON (PostgreSQL)                    │
│                                              │
│  SELECT DISTINCT ON (customer_id)            │
│    customer_id, order_date, total            │
│  FROM orders                                 │
│  ORDER BY customer_id, order_date DESC;      │
│                                              │
│  Result:                                     │
│    1 | 2026-09-12 | 199.99  (latest for 1)  │
│    2 | 2026-03-10 |  49.99  (latest for 2)  │
│    3 | 2026-04-05 | 299.99  (latest for 3)  │
│                                              │
│  First row per group, determined by ORDER BY.│
│                                              │
└──────────────────────────────────────────────┘

The DISTINCT Trap

┌──────────────────────────────────────────────┐
│  THE DISTINCT TRAP                           │
│                                              │
│  SELECT DISTINCT city, customer_id           │
│  FROM customers;                             │
│                                              │
│  → Every row, because customer_id is unique. │
│                                              │
│  DISTINCT applies to ALL selected columns.   │
│  To deduplicate a single column, select it   │
│  alone, or use GROUP BY.                     │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
DISTINCT	Remove duplicate rows
Applies to	All selected columns
COUNT(DISTINCT col)	Count unique values
DISTINCT ON	First row per group (PostgreSQL)
Portable alternative	ROW_NUMBER()
GROUP BY	Aggregation, not deduplication
NULL handling	NULL is a distinct value
Performance	Requires sort or hash
Use case	Unique values, unique counts

Key takeaways:

• The DISTINCT clause removes duplicate rows from the result set. It applies to the entire row, not to a single column. The SELECT DISTINCT city, state query returns unique combinations of city and state, not unique cities .
• COUNT(DISTINCT column) counts the number of unique non-null values. The NULL values are ignored. The function is the natural companion to the DISTINCT clause for counting unique values .
• The DISTINCT clause and the GROUP BY clause are different. The DISTINCT clause removes duplicates. The GROUP BY clause groups rows and allows aggregation. The DISTINCT clause is a shorthand for GROUP BY when no aggregation is needed .
• PostgreSQL provides DISTINCT ON. The clause returns the first row for each unique combination of the specified columns. The ORDER BY clause determines which row is first. The feature is PostgreSQL-specific .
• ROW_NUMBER() is the portable alternative to DISTINCT ON. The window function assigns a row number to each row within each group. The outer query filters by the row number. The approach works in every database .
• The DISTINCT clause is not a fix for a bad query. It removes duplicates, not irrelevant rows. If a join produces duplicates because the join condition is wrong, the fix is to correct the join, not to add DISTINCT .
• The DISTINCT clause has a cost. The database must sort the result set or use a hash table to find the duplicates. For a large result set, the cost is significant. Use DISTINCT when duplicates are expected, not as a reflex.

Remember: The DISTINCT clause removes duplicate rows. It applies to all selected columns. COUNT(DISTINCT col) counts unique values. DISTINCT ON returns the first row per group in PostgreSQL. ROW_NUMBER() is the portable alternative. DISTINCT and GROUP BY are different tools for different purposes. The DISTINCT clause deduplicates. The GROUP BY clause aggregates. Use the right tool for the right job.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!