DISTINCT

Hook: DISTINCT is the SQL bouncer: it only lets through rows that look unique after your SELECT list is applied.

Question: What does DISTINCT do in SQL?

Answer: DISTINCT removes duplicate rows from the result set. It compares the columns you selected as one whole row, not each column separately. That means SELECT DISTINCT email and SELECT DISTINCT email, city can return very different results.

Interview-Ready Answer: I use DISTINCT when I want one copy of each unique result row. The important detail is that it deduplicates the final selected columns as a tuple, so SELECT DISTINCT email is not the same as SELECT DISTINCT email, id. Under the hood the database usually hashes or sorts the rows to remove duplicates, so it can be more expensive than it looks on large data.

What DISTINCT really means

Detailed Explanation: SQL normally keeps duplicates unless you ask it not to. DISTINCT says, “show me only unique rows from this projection.” A projection means the columns or expressions in the SELECT list. So the database does not dedupe the original table first; it dedupes the rows after the selected columns are computed.

1. The database reads candidate rows from FROM, joins, and WHERE.
2. It evaluates the expressions in the SELECT list.
3. It compares the full selected row as a single unit.
4. If two rows match on every selected column, one is removed.
5. The remaining unique rows are returned, and then ORDER BY is applied to that final set.

How the engine usually does it

Most engines use one of two physical strategies: a hash-based distinct or a sort-based distinct. A hash method stores seen rows in memory and checks each new row against that set. A sort method orders the rows first, then removes neighbors that are equal. Both are correct; the optimizer chooses based on data size, available indexes, and memory.

When to use it: use DISTINCT for a clean unique list, like unique emails, countries, or category names. Use GROUP BY when you also need aggregates such as COUNT, SUM, or MAX. In many databases, GROUP BY without aggregates can look similar to DISTINCT, but DISTINCT is usually clearer when you only want deduplication.

Important gotchas

• It applies to the whole row. If you add a unique column like id, duplicates may disappear from the dedupe logic because every row is now different.
• NULL values collapse together. For duplicate removal, SQL treats multiple NULLs as one distinct value in the result.
• ORDER BY can be tricky. Many engines require the ordered column to appear in the select list when DISTINCT is used, because the sort happens after deduplication.
• It can be expensive. On 1,000,000 rows, a hash distinct may be fast if there are only 10,000 unique rows, but it can spill to disk if the unique set is huge. A sort distinct often needs roughly O(n log n) work and may write temp files too.

Memory model: if the engine uses a hash table, the space is roughly proportional to the number of unique rows, not total rows. That is why DISTINCT on a column with almost every value unique can be memory-heavy. If an index already orders the needed columns, some databases can avoid extra work by reading in sorted order.

Memory Hook: Think of DISTINCT as stamping each unique outfit once. If the selected columns change even a little, it is a new outfit and gets a new stamp.

Real-World Example: Imagine a marketing export job in a checkout service that sends a daily list of customer emails to a campaign tool. The team writes SELECT DISTINCT email, customer_id because they want unique emails, but the extra customer_id makes every row unique. The campaign tool receives duplicate emails, some users get two copies of the same message, and support tickets spike.

In the logs, the job looks healthy: row count is high, no SQL error appears, and the export file is created on time. The hidden bug is semantic, not technical: the query is syntactically valid, but the deduplication key is wrong. The fix is to select only the business key you truly want unique, such as email, or to use a proper grouping/window approach if you also need the newest profile data.

The symptom pattern is classic: duplicate recipients, repeated inserts into a downstream table, and complaint emails like “I got this twice.” Interviewers love this example because it shows that DISTINCT is not a magic anti-bug button; it only dedupes exactly what you ask it to dedupe.

Follow-up & Tricky Questions:

• When would you use GROUP BY instead of DISTINCT? Use GROUP BY when you need aggregates like counts or sums. Use DISTINCT when you only want unique rows and do not need calculations.
• Does DISTINCT remove duplicates before or after WHERE? After WHERE. The filter reduces the candidate rows first, then DISTINCT removes duplicates from what is left.
• How does DISTINCT treat NULL? For duplicate elimination, multiple NULL values are treated as the same distinct output value, so you usually get one NULL back.
• Can DISTINCT be used with joins? Yes, but be careful: it can hide a bad join that creates extra rows. It is better to fix the join condition than to cover the symptom with DISTINCT.
• Why can DISTINCT be slow? Because the database may need to sort or hash a large row set. If many rows are unique, memory use and temp-file I/O can grow quickly.
• Does DISTINCT pick the newest row? No. It does not choose by time or priority; it only removes identical selected rows. To pick the latest row per key, you usually need a window function.
• Can I sort by a column not in the select list? Many engines restrict this when DISTINCT is present, because the final row shape is defined by the select list. The safe habit is to sort by selected columns.
• Does SELECT DISTINCT * always mean one row per business entity? No. It means one row per fully identical row across all columns. If any column differs, the rows stay separate.
• Is DISTINCT the same as UNIQUE? Not exactly. UNIQUE is a constraint that enforces uniqueness in stored data, while DISTINCT is a query-time operation that filters the result set.
• Does DISTINCT change the table? No. It only changes what the query returns; it does not delete or update rows in the table.
• Does DISTINCT remove duplicates from each column separately? No. That is a common mistake. It compares the entire selected row, so SELECT DISTINCT city, country keeps unique pairs, not unique cities and unique countries independently.
• Will SELECT DISTINCT id, email dedupe duplicate emails? Usually not, because different id values make the rows different. If your dedupe key is email, select only email or group by email.

Common Mistakes:

• Adding a unique column by habit. Correction: if you include id, you may destroy the dedupe logic. Select only the columns that define uniqueness.
• Using DISTINCT to hide a bad join. Correction: fix the join cardinality first; dedupe only at the end if the business meaning truly requires it.
• Expecting a specific row to survive. Correction: DISTINCT does not mean “keep the latest” or “keep the biggest”; it only removes identical projected rows.
• Forgetting about NULL. Correction: if you want only real values, add WHERE col IS NOT NULL before DISTINCT.

Memory Hook: “Distinct checks the outfit, not the passport.” The outfit is the selected columns; if the outfit is the same, one row stays and the rest go away.

Cheat Sheet:

• DISTINCT removes duplicate result rows.
• It works on the full selected row, not each column alone.
• NULLs collapse to one output row.
• GROUP BY is better when you need aggregates.
• It can be expensive on large, high-cardinality data.
• Use the smallest set of columns that defines uniqueness.

Practice Tasks:

• Write a query that lists unique countries from a customers table.
• Change the query to list unique city, country pairs and compare the row counts.
• Take a query that uses SELECT DISTINCT id, email and fix it so it truly dedupes by email.