What does DISTINCT do and what is its cost? | Interview Question

Q: What does DISTINCT do and what is its cost?

**Target Audience:** Beginners who use `SELECT DISTINCT` and want to know what it really does and why it can make a query slow. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; plans were captured on both. MySQL notes are marked as not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output and plans were produced by actually running the queries; the **Verified** section quotes them in full. ## 1. Why This Even Matters — A Story First A wedding planner has a pile of 300 RSVP cards and needs the list of **towns** guests are coming from, to book hotels. She cannot just read the cards top to bottom and write down each town, because Pune would appear 80 times. She has two options: - **Sort the cards by town.** Now every Pune card is next to every other Pune card, and she writes dow

The problem

Question presented to candidate: "What does SELECT DISTINCT actually do, row by row? And when a colleague adds DISTINCT to a slow report query to get rid of duplicates, what does it cost the database, and what would you suggest instead?"

What a strong answer should cover:

DISTINCT removes duplicate result rows, comparing the whole SELECT list, not one column; for DISTINCT, NULLs count as equal to each other
It runs after SELECT and before ORDER BY, which is why PostgreSQL requires ORDER BY expressions to appear in the select list
Cost: the engine must find duplicates, by sorting (Sort + Unique) or hashing (HashAggregate), using memory proportional to the distinct rows and spilling to disk past work_mem; an index that already delivers rows in order can avoid that work
DISTINCT used to hide join fan-out is a smell: EXISTS (a semi-join) or fixing the join is cheaper and more correct
Related forms: COUNT(DISTINCT col), GROUP BY as an equivalent, UNION vs UNION ALL, and PostgreSQL's DISTINCT ON for "one row per key"

Code / implementation expected: Optional — a DISTINCT query and its plan, or a join fixed with EXISTS instead of DISTINCT.

Schema used by the examples:

distinct

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who use SELECT DISTINCT and want to know what it really does and why it can make a query slow. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; plans were captured on both. MySQL notes are marked as not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. All output and plans were produced by actually running the queries; the Verified section quotes them in full.

1. Why This Even Matters — A Story First

A wedding planner has a pile of 300 RSVP cards and needs the list of towns guests are coming from, to book hotels. She cannot just read the cards top to bottom and write down each town, because Pune would appear 80 times. She has two options:

The solution is hidden so you can try first

14 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLDISTINCT compares whole result rows, and treats NULLs as one value

SQLCOUNT(DISTINCT col) ignores NULLs; counting distinct pairs needs a subquery

SQLDISTINCT as a band-aid for join fan-out, and the EXISTS fix

SQLSQLite: DISTINCT needs a temporary B-tree, unless an index already provides the order

PostgreSQL: DISTINCT planned as a HashAggregate

EXPLAIN (COSTS OFF)
SELECT DISTINCT customer_id, product FROM orders;

PostgreSQL: with hash aggregation disabled, the same DISTINCT becomes Sort + Unique

SET enable_hashagg = off;
EXPLAIN (COSTS OFF)
SELECT DISTINCT customer_id, product FROM orders;

PostgreSQL DISTINCT ON: the latest order per customer

SELECT DISTINCT ON (customer_id) customer_id, product, amount, placed
FROM orders
ORDER BY customer_id, placed DESC, id DESC;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.