1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)

SQL DISTINCT removes duplicate rows from a query result. Learn how it works on one and many columns, how it treats NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, and the traps that make it hide real bugs, with every query run and its output shown.

9 min read
SQL DISTINCT: how to remove duplicate rows and its traps

Key takeaways

  • SQL DISTINCT removes duplicate rows from the result, comparing every selected column together.
  • DISTINCT treats all NULLs as one value, but COUNT(DISTINCT col) ignores NULLs completely.
  • If you need DISTINCT to fix a join, the join is usually wrong. EXISTS is often the cleaner fix.
  • An index on the column made DISTINCT about 6 times faster on 500,000 rows in our test.

You run a report, see the same customer listed three times, and type SELECT DISTINCT. The duplicates vanish and the report looks right. Sometimes that is the correct fix. Often it is a bandage over a bad join or a misunderstanding of the data. This guide shows exactly what SQL DISTINCT does, where it shines, and the traps that make it return confidently wrong answers.

Every query below was run on SQLite 3.50.4 and the output is copied from the run. DISTINCT is standard SQL, so the same queries work in MySQL, PostgreSQL, SQL Server and Oracle; we flag the few places where databases differ.

What does DISTINCT do in SQL?

DISTINCT goes right after SELECT and tells the database to return each unique row only once:

SELECT DISTINCT column1, column2
FROM table_name
WHERE condition;

It runs late in the query: after FROM, WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. That order explains most of its behavior: it de-duplicates whatever rows the rest of the query produced, using only the columns you selected.

The sample data

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, city TEXT,
                     product TEXT, amount INT);
INSERT INTO orders (customer, city, product, amount) VALUES
 ('Asha','Pune','Laptop',900), ('Ravi','Delhi','Mouse',20),
 ('Asha','Pune','Mouse',20),   ('Meera','Pune','Laptop',900),
 ('Ravi','Delhi','Mouse',20),  ('Kabir',NULL,'Desk',300),
 ('Lina',NULL,'Lamp',40),      ('Meera','pune','Lamp',40);

Eight orders, with deliberate problems: a repeated order from Ravi, two missing cities, and one city typed in lower case.

DISTINCT on one column

SELECT DISTINCT city FROM orders;

-- city
-- Pune
-- Delhi
-- NULL
-- pune

Eight rows became four. Notice two things. First, the two NULLs collapsed into one row. Second, Pune and pune are different values here, because SQLite compares text case-sensitively by default. MySQL with its usual case-insensitive collation would treat them as one. Clean the data, or use DISTINCT LOWER(city), when case should not matter.

DISTINCT on multiple columns

SELECT DISTINCT customer, city FROM orders ORDER BY customer;

-- Asha  | Pune
-- Kabir | NULL
-- Lina  | NULL
-- Meera | Pune
-- Meera | pune
-- Ravi  | Delhi

With several columns, DISTINCT looks at the combination. Asha appears once because both orders from Asha are Asha plus Pune. Meera appears twice because the city spelling differs. A row is removed only when every selected column matches another row.

The DISTINCT(column) myth

Many people write SELECT DISTINCT(city), amount and expect unique cities. We tested it on a small table:

SELECT DISTINCT(city), amount FROM t;

-- Pune  | 5
-- Delhi | 9
-- Pune  | 1

Pune still appears twice. DISTINCT is not a function; the parentheses just wrap the first column, and de-duplication still applies to the whole row. To get one row per city with an amount, decide which amount you want and use GROUP BY city with MAX(amount) or similar.

How DISTINCT handles NULL

In most SQL comparisons, NULL is not equal to NULL. DISTINCT is an exception: for de-duplication, all NULLs count as the same value, so you get one NULL row, as our city query showed. GROUP BY behaves the same way. If NULL in your result is noise, filter it out with WHERE city IS NOT NULL. Our SQL NULL values guide covers the other places NULL behaves differently.

COUNT DISTINCT

SELECT COUNT(*)                     AS all_rows,
       COUNT(city)                  AS non_null,
       COUNT(DISTINCT city)         AS distinct_cities,
       COUNT(DISTINCT LOWER(city))  AS distinct_ci
FROM orders;

-- all_rows | non_null | distinct_cities | distinct_ci
-- 8        | 6        | 3               | 2

This one query shows four different answers to “how many cities?”. COUNT(DISTINCT city) returned 3, not 4, because unlike SELECT DISTINCT, aggregate functions skip NULLs entirely. Folding case brings it down to 2, the true number of cities. Be precise about which count your report means.

DISTINCT vs GROUP BY

SELECT city, COUNT(*) AS n FROM orders GROUP BY city;

-- NULL  | 2
-- Delhi | 2
-- Pune  | 3
-- pune  | 1

SELECT DISTINCT city and SELECT city ... GROUP BY city return the same set of values, and many optimizers plan them alike, although in our SQLite timing test below GROUP BY was slower without an index. The main difference is intent. Use DISTINCT when you only want unique values. Use GROUP BY when you also need an aggregate per group, such as a count or a total. Our GROUP BY and HAVING guide goes deeper.

The SUM DISTINCT trap

SELECT SUM(amount) AS sum_all, SUM(DISTINCT amount) AS sum_distinct
FROM orders;

-- sum_all | sum_distinct
-- 2240    | 1260

SUM(DISTINCT amount) adds each unique amount once: 900 + 20 + 300 + 40. It does not remove duplicate orders. Two different customers who both spent 900 get merged, and your revenue drops by almost half. If you want to exclude duplicate orders, de-duplicate the rows first, then sum.

The join trap: DISTINCT hiding a bug

Add a tags table where one product has two tags:

CREATE TABLE tags (product TEXT, tag TEXT);
INSERT INTO tags VALUES ('Laptop','tech'), ('Laptop','sale'), ('Mouse','tech');

SELECT o.customer FROM orders o
JOIN tags t ON t.product = o.product
WHERE t.tag IN ('tech','sale') ORDER BY 1;

-- Asha, Asha, Asha, Meera, Meera, Ravi, Ravi

The join multiplies rows: every laptop order matches two tags. Adding DISTINCT makes the list look right, but any SUM or COUNT built on that join is still inflated. When you only need to know whether a match exists, use EXISTS, which never multiplies rows:

SELECT DISTINCT o.customer FROM orders o
WHERE EXISTS (SELECT 1 FROM tags t
              WHERE t.product = o.product AND t.tag IN ('tech','sale'))
ORDER BY 1;

-- Asha, Meera, Ravi

Here DISTINCT only removes customers with several orders, which is what we actually want. See EXISTS vs IN and SQL joins explained for more.

Tip: when you reach for DISTINCT, first ask why duplicates appeared. If the answer is a join, fix the join.

Keeping one full row per duplicate

DISTINCT can only return the columns it compares. To delete or report true duplicates while keeping an ID, number the copies with a window function:

SELECT id, customer, product
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer, product
                                   ORDER BY id) AS rn
      FROM orders) AS d
WHERE rn = 1 ORDER BY id;

-- 1 Asha Laptop | 2 Ravi Mouse | 3 Asha Mouse | 4 Meera Laptop
-- 6 Kabir Desk  | 7 Lina Lamp  | 8 Meera Lamp

Ravi’s repeat order, id 5, is gone, and every kept row still has its ID. That is the starting point for deleting duplicate rows in SQL; ROW_NUMBER explains the window function. PostgreSQL also offers DISTINCT ON (customer, product) for the same job; we did not run that one.

DISTINCT and ORDER BY

Sorting by a column you did not select is ambiguous with DISTINCT: if Pune has amounts 5 and 1, which one decides its position? SQLite accepted SELECT DISTINCT city FROM t ORDER BY amount and simply picked one. PostgreSQL rejects it with an error that ORDER BY expressions must appear in the select list (not run here; check your database). Sort by a selected column or an aggregate to keep results predictable.

DISTINCT performance

We loaded 500,000 events for 5,000 users and timed each query (best of three):

Query No index Index on user_id
SELECT DISTINCT user_id 86.6 ms 13.8 ms
SELECT user_id ... GROUP BY user_id 143.8 ms 19.0 ms
SELECT COUNT(DISTINCT user_id) 85.3 ms 12.7 ms

Without an index, SQLite scanned the table and built a temporary B-tree to track seen values. With one, the plan switched to a covering-index scan and DISTINCT ran about 6 times faster. On large tables, de-duplicating needs sorting or hashing, so select only the columns you need and index the ones you de-duplicate often. Our SQL indexes guide explains why.

SQL DISTINCT checklist

  • Select only the columns that define a duplicate.
  • Decide whether NULL should appear, and whether case matters.
  • Never use SUM(DISTINCT ...) to remove duplicate rows.
  • If a join caused the duplicates, fix it with EXISTS or a pre-aggregated subquery.
  • Use ROW_NUMBER when you need to keep a full row or an ID.
  • Prefer UNION ALL over UNION when duplicates are impossible; UNION runs an implicit DISTINCT (see UNION vs UNION ALL).

Final thoughts

SQL DISTINCT is simple: it removes rows that match on every selected column. The skill is knowing when that is the right fix. Use it for unique value lists and COUNT DISTINCT, watch how it treats NULLs and letter case, and treat a DISTINCT added to make a join look right as a warning sign. Check the cause of duplicates first, and your reports will be both clean and correct.

Frequently asked questions

Does DISTINCT apply to all columns in SQL?

Yes. DISTINCT compares the full row of selected columns. A row is removed only when every selected column matches another row, even if you wrap one column in parentheses.

Does SELECT DISTINCT include NULL values?

Yes. All NULLs are treated as one value, so the result contains a single NULL row. COUNT DISTINCT is different and ignores NULLs completely.

Is DISTINCT faster than GROUP BY?

Often they perform about the same. In our SQLite test DISTINCT took 86.6 ms and GROUP BY 143.8 ms on 500,000 rows, but adding an index made both several times faster, a much bigger win.

How do I count distinct values in SQL?

Use COUNT(DISTINCT column). It counts unique non-NULL values. To count unique combinations, count rows from a subquery that selects DISTINCT on those columns.

Why does DISTINCT still show duplicates?

Usually another selected column differs, such as an ID, a timestamp or a value with different letter case or spaces. Select only the columns that define a duplicate, or clean the data first.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
Software Development

CORS Explained: Preflight Requests, Headers and How to Fix CORS Errors

CORS explained: origins, simple and preflight requests, the response headers your server must send, credentials, and how to fix common…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Databases & SQL

SQL CAST Explained: Convert Data Types With Tested Examples

SQL CAST explained with tested examples: text to number, truncation, integer division, numbers stored as text, dates, bad input and…

Read article →
Databases & SQL

SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)

SQL DISTINCT explained with tested queries: one and many columns, NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, the SUM DISTINCT…

Read article →
Software Development

JavaScript Reduce Explained: 12 Practical Examples

JavaScript reduce explained step by step with 12 practical examples: sums, tallies, grouping, Maps, pipelines, sequential promises and the empty…

Read article →
Software Development

Python Random Module: randint, choice, seed and More

The Python random module explained: randint, randrange, choice, choices, sample, shuffle, seed, weights and when to use secrets instead, with…

Read article →
Software Development

Python Slicing Explained: Start, Stop and Step With Examples

Python slicing explained with start, stop and step, negative indexes, reversing, copying and slice assignment, with tested examples and a…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
Software Development

URL Encoding Explained: Spaces, Plus Signs and UTF-8

URL encoding explained with tested Python and JavaScript code: percent encoding, reserved characters, %20 vs plus, UTF-8 bytes, encodeURI vs…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Software Development

JavaScript Set: Union, Intersection and Removing Duplicates

JavaScript Set explained with tested examples: add, has, delete, deduplicating arrays, the new union and intersection methods, equality rules and…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →