What Does the DISTINCT Keyword Do in SQL?
Learn how the SQL DISTINCT keyword removes duplicate rows, works across multiple columns, handles NULLs, and powers COUNT(DISTINCT) for unique value lists.
Expected Interview Answer
The DISTINCT keyword removes duplicate rows from a query's result set, so each unique combination of the selected columns appears only once.
It is placed right after SELECT and applies to all listed columns together, not to a single column in isolation. The database compares the full set of selected values row by row and keeps only the first occurrence of each unique combination, treating NULLs as equal to one another for this comparison. DISTINCT is applied after the WHERE clause filters rows but conceptually before ORDER BY sorts the deduplicated output.
- Eliminates duplicate rows from results
- Produces clean lists of unique values
- Works across multiple columns as a combination
- Simplifies counting unique entries with COUNT(DISTINCT ...)
- Improves readability of reports and dropdowns
AI Mentor Explanation
Imagine you want a list of every country that has ever played a Test match. Your ball-by-ball log mentions India thousands of times, but you only want each nation named once. DISTINCT is the scorer who scans that huge log and writes down each country a single time, so the final honour board lists unique teams rather than repeating India for every delivery they ever faced.
Step-by-Step Explanation
Step 1
Write the base SELECT
Start with the columns you want, for example SELECT country FROM matches.
Step 2
Add DISTINCT after SELECT
Place DISTINCT immediately after SELECT so it applies to the whole column list that follows.
Step 3
Understand multi-column scope
With several columns, uniqueness is judged on the full combination of their values, not each column separately.
Step 4
Combine with COUNT when needed
Use COUNT(DISTINCT column) to count unique values rather than total rows.
Step 5
Order the result
Apply ORDER BY after DISTINCT to sort the already-deduplicated rows for readability.
What Interviewer Expects
- Knowing DISTINCT removes duplicate rows, not values within one column
- Understanding it applies to the entire selected column list together
- Ability to use COUNT(DISTINCT ...) correctly
- Awareness that NULLs are treated as equal by DISTINCT
- Understanding where DISTINCT sits relative to WHERE and ORDER BY
Common Mistakes
- Thinking DISTINCT de-duplicates only the first column when several are selected
- Confusing DISTINCT with GROUP BY without knowing when each is appropriate
- Believing DISTINCT can be applied to just one column while others remain duplicated
- Assuming multiple NULLs are kept as separate distinct rows
- Adding DISTINCT everywhere as a fix, hurting performance instead of fixing the query logic
Best Answer (HR Friendly)
“DISTINCT is a SQL keyword that removes duplicate rows from your results so each unique entry shows up only once. It is handy when you want a clean list, like every unique city or customer, instead of the same value repeated many times.”
Code Example
-- Unique list of cities
SELECT DISTINCT city
FROM customers;
-- Uniqueness across two columns (city + country combination)
SELECT DISTINCT city, country
FROM customers;
-- Count how many unique countries exist
SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;Follow-up Questions
- What is the difference between DISTINCT and GROUP BY?
- How does DISTINCT treat NULL values?
- Does DISTINCT apply to one column or all selected columns?
- How does COUNT(DISTINCT column) differ from COUNT(column)?
- Can DISTINCT affect query performance on large tables?
MCQ Practice
1. What does SELECT DISTINCT do when multiple columns are listed?
DISTINCT evaluates uniqueness across the entire combination of selected columns, keeping each unique tuple once.
2. How does DISTINCT treat multiple NULL values in a column?
For DISTINCT, NULLs are considered equal to each other, so repeated NULLs collapse into a single row.
3. Which query counts the number of unique departments?
COUNT(DISTINCT department) counts how many different department values exist, ignoring duplicates.
Flash Cards
What does DISTINCT remove? — Duplicate rows from the result set, keeping one row per unique combination of selected columns.
Does DISTINCT apply per column or to all columns? — To all selected columns together — uniqueness is judged on the full combination.
How does DISTINCT handle NULLs? — It treats all NULLs as equal, so repeated NULLs collapse into a single distinct row.
How do you count unique values? — Use COUNT(DISTINCT column) to count distinct non-NULL values in that column.