Understanding DISTINCT in SQL
What is DISTINCT?
DISTINCT is a modifier you add right after SELECT to tell the database "give me only unique rows in the result — collapse any exact duplicates into one." If you select a single column, "unique" means unique values in that column. If you select several columns, "unique" means unique combinations across all of them together — which is exactly the case in this problem, where a (city, country) pair only counts as a duplicate if both values match another row.
Why Interviewers Ask This Question
From a hiring perspective, this is a fast, low-friction way to test something that trips up a surprising number of candidates: whether you understand DISTINCT as a row-level operation. Anyone can recite "DISTINCT removes duplicates." Fewer candidates correctly predict what happens on two or three columns together, and fewer still can cleanly explain when DISTINCT stops being enough and you need GROUP BY instead. It's a small problem that reveals real understanding quickly, which is exactly what a 30-minute interview slot needs.
Real-world Example
This exact shape of problem — deduplicating a combination of columns — shows up constantly outside of interviews too:
- Customer database: unique (city, country) pairs your customers are actually based in, for sales territory planning.
- Sales reports: unique (product, region) combinations that have ever recorded a sale.
- Unique visitors: unique (user_id, page) pairs in a web analytics log, to avoid counting the same pageview twice.
- Cities and countries: building a clean, deduplicated location picker for a signup form from raw transactional data.
Common Mistakes
- Forgetting ORDER BY. DISTINCT does not guarantee any output order — without an explicit ORDER BY, the row order is whatever the engine happens to produce internally, which can even change between runs.
- Misunderstanding DISTINCT as per-column. A common wrong assumption is that
SELECT DISTINCT city, countrydeduplicates city and country separately. It doesn't — uniqueness is evaluated on the whole row of selected columns together. - Duplicate rows vs. duplicate columns. DISTINCT operates on rows, not on individual columns. You can't ask DISTINCT to deduplicate one selected column while leaving another column's repeats untouched.
- NULL handling assumptions. Some candidates assume NULL breaks DISTINCT or gets silently dropped. In practice, SQL treats all NULLs as equal to each other for DISTINCT's purposes, so a NULL (or a NULL combination) is deduplicated like any other value — not removed.
Time Complexity
Most database engines implement DISTINCT with either a sort (group equal rows together, then walk through and keep the first of each group) or a hash-based approach (bucket rows by a hash of the selected columns and compare within buckets). Both are roughly O(n log n) to O(n) depending on the engine and whether a supporting index exists — in practice, the cost scales with the number of rows scanned before deduplication, not the number of unique rows returned.
Alternative Solution: GROUP BY
Yes — GROUP BY can solve this exact problem:
SELECT city, country FROM customers GROUP BY city, country ORDER BY country, city;
For pure deduplication like this, DISTINCT and GROUP BY city, country return identical results. The difference is intent and flexibility: DISTINCT communicates "just deduplicate this" more directly and is usually marginally simpler to read; GROUP BY is the better choice the moment you also need an aggregate alongside the grouping — for example COUNT(*) per (city, country) pair — since DISTINCT has no equivalent for computing a per-group value. If you don't need a per-group aggregate, prefer DISTINCT for clarity; reach for GROUP BY the moment you do.
SQL Learning Roadmap
DISTINCT sits early in a practical SQL roadmap — right after you're comfortable filtering and sorting, and just before aggregation with GROUP BY. Here's where this problem fits:
- ✓ SELECT
- ✓ WHERE
- ✓ ORDER BY
- ● DISTINCT
- → GROUP BY
- → HAVING
- → JOINS
- → Window Functions
- → CTE
- → Subqueries
Frequently Asked Questions
What is DISTINCT in SQL?
DISTINCT is a keyword used with SELECT to remove duplicate rows from a query's result set, returning only unique combinations of the selected columns.
Can DISTINCT work on multiple columns?
Yes. When DISTINCT is applied to more than one column, uniqueness is evaluated across the whole combination of those columns together, not on each column independently.
What is the difference between DISTINCT and GROUP BY?
DISTINCT simply removes duplicate rows from the output. GROUP BY groups rows that share values so you can run aggregate functions (COUNT, SUM, AVG) per group. If you don't need an aggregate, DISTINCT is simpler; if you do, GROUP BY is the right tool.
Does DISTINCT remove NULL values?
No. SQL treats all NULLs as equal for the purposes of DISTINCT, so a single NULL (or NULL combination) is kept in the result — DISTINCT does not filter NULLs out.
Is DISTINCT expensive to run?
DISTINCT typically requires sorting or hashing the result set to find duplicates, which adds overhead roughly proportional to the number of rows scanned. On an indexed, well-scoped query it is usually fast; on very large unindexed tables it can be one of the more expensive steps in a query plan.
How is DISTINCT usually asked about in interviews?
Interviewers commonly ask candidates to return unique combinations of two or more columns (like this problem), or to explain why COUNT(DISTINCT column) differs from COUNT(column) — testing whether you understand DISTINCT as a row-level, not a single-value, operation.
Can DISTINCT be used with COUNT?
Yes — COUNT(DISTINCT column) counts the number of unique values in a column, ignoring duplicates. This is one of the most common real-world uses of DISTINCT.
Can DISTINCT be used after a JOIN?
Yes, and it is common — joins frequently produce duplicate rows (for example, one customer row repeated once per order), and DISTINCT (or GROUP BY) is often used afterward to collapse those back to unique combinations.
Does column order matter with DISTINCT?
No — SELECT DISTINCT city, country and SELECT DISTINCT country, city return the same set of unique row combinations; only the column order in the output changes, not which rows are considered duplicates.
Should I always add ORDER BY with DISTINCT?
Not strictly required, but strongly recommended — DISTINCT does not guarantee any particular row order, so if the result needs to be presented consistently (as in this problem, ordered by country then city), an explicit ORDER BY is necessary.
Continue Learning SQL
Keep building your SQL foundation — from the basics this problem assumes, through to where DISTINCT leads next:
People Also Solve
Learners who solved this DISTINCT problem also practiced: