DISTINCT Deduplication
Overview
Databases often contain massive amounts of repetitive data. If you run SELECT country FROM users;, and you have 10,000 users from the USA, the database will literally print 'USA' 10,000 times. The DISTINCT keyword is a deduplication filter. It intercepts the final result set, mathematically scans it, and brutally eliminates all duplicate rows, ensuring every row returned is 100% unique.
Syntax
-- 1. Basic Deduplication
-- Returns a clean list of countries we operate in.
SELECT DISTINCT country
FROM users;
-- 2. Multi-Column Deduplication
-- Returns unique COMBINATIONS of Country + City.
-- (e.g., USA-NY, USA-CA, UK-London)
SELECT DISTINCT country, city
FROM users;
-- 3. Counting Unique Values!
-- How many DIFFERENT countries do our users live in?
SELECT COUNT(DISTINCT country) AS unique_country_count
FROM users;Common Pitfalls
- Using
DISTINCTas a lazy fix for bad Joins. If your query is suddenly returning 5,000 duplicate rows because of a poorly writtenJOIN, slappingDISTINCTon it will 'fix' the output, but it forces the database to do a massive amount of hidden sorting and hashing to deduplicate the mess, destroying query performance. Fix your JOIN logic instead. - Misunderstanding multi-column distinct.
SELECT DISTINCT col1, col2does NOT return unique col1s and unique col2s independently. It evaluates them as a single, combined row entity.
Interview Questions
SELECT DISTINCT department and SELECT department ... GROUP BY department?Visually, they output the exact same unique list. However, GROUP BY prepares the data for mathematical aggregation (SUM, AVG), while DISTINCT is simply a post-processing filter designed purely to drop identical rows.
Real-World Example
Generating a dropdown filter for an E-commerce UI that dynamically shows all unique categories currently in stock.
SELECT DISTINCT category_name
FROM products
WHERE stock_count > 0
ORDER BY category_name ASC;Check Your Knowledge
Test your understanding of DISTINCT Deduplication with these quick questions.