Introduction
In real-world relational databases, records frequently repeat values across rows. For instance, in an e-commerce platform with 500,000 orders, thousands of customers might reside in Mumbai, Delhi, or London. If you query the customer locations, a standard SELECT city FROM customers; query will return 500,000 rows where city names repeat over and over.
When building filter dropdowns, demographic reports, or categorical summaries, you don’t want 500,000 repeated entries — you need a clean, concise list containing each unique value exactly once. This is the exact problem that DISTINCT solves.
What Does DISTINCT Mean?
In SQL, the DISTINCT keyword acts as a deduplication filter on the query's output result set. It examines the returned rows and strips away all duplicate entries, keeping only the first occurrence of each unique value or combination of values.
Original Table Rows (city) | Standard Query (SELECT city) | Deduplicated Query (SELECT DISTINCT city) |
|---|---|---|
| Row 1: Mumbai | Mumbai | Mumbai |
| Row 2: Mumbai | Mumbai | Mumbai (Removed Duplicate) |
| Row 3: Delhi | Delhi | Delhi |
| Row 4: Pune | Pune | Pune |
| Row 5: Mumbai | Mumbai | Mumbai (Removed Duplicate) |
Mumbai
Delhi
Pune
Mumbai
Delhi
Pune
Basic DISTINCT Syntax
The syntax for DISTINCT is straightforward: place the keyword directly after SELECT:
FROM table_name;
Let us break down each part:
SELECT: The data extraction command.DISTINCT: Instructs the engine to collapse identical output rows into a single unique row.column_name: The column (or list of columns) to extract and evaluate.FROM table_name: The database table being queried.;: Statement terminator.
DISTINCT With One Column
When you select a single column, DISTINCT checks each value individually:
FROM customers;
If the customers table contains 50,000 rows across 12 unique cities, this query returns exactly 12 rows.
DISTINCT With Multiple Columns (Tuple Uniqueness)
This is one of the most important concepts for SQL beginners: DISTINCT applies to all columns listed in the SELECT clause as a combined unit (tuple), not just the first column.
FROM employees;
In this query, SQL checks whether the combination of (city, department) has been seen before:
| Row | city | department | Combined Tuple | Result Set Action |
|---|---|---|---|---|
| 1 | Mumbai | Sales | (Mumbai, Sales) | Retained (1st time seen) |
| 2 | Mumbai | Sales | (Mumbai, Sales) | Dropped (Duplicate tuple) |
| 3 | Mumbai | HR | (Mumbai, HR) | Retained (Different department!) |
| 4 | Delhi | Sales | (Delhi, Sales) | Retained (Different city!) |
Mumbai + Sales and Mumbai + HR are considered distinct result rows because their department values differ, even though both share the same city.Mumbai | Sales (Duplicate)
Mumbai | HR
Delhi | Sales
Mumbai | HR
Delhi | Sales
DISTINCT vs Normal SELECT
Compare the execution outcomes directly:
| Feature | SELECT city FROM customers; | SELECT DISTINCT city FROM customers; |
|---|---|---|
| Output Row Count | Always equals total rows in table | Equals number of unique city values |
| Duplicate Handling | Preserves all repeating values | Eliminates all repeating values |
| Processing Cost | Minimal (direct streaming scan) | Requires hashing/sorting buffer in memory |
| Primary Use Case | Retrieving raw transactional data | Building category lists, filters, audits |
DISTINCT With NULL Values
In SQL, NULL represents missing or unknown data. In logical comparisons, NULL = NULL evaluates to UNKNOWN.
However, for the DISTINCT clause, all SQL engines (PostgreSQL, MySQL, SQLite, Oracle, SQL Server) treat multiple NULL values as identical duplicates of one another:
SELECT DISTINCT referral_code
FROM customers;
This query will return the unique referral codes plus exactly one single NULL row representing all missing values.
DISTINCT and Result Ordering (DISTINCT ≠ SORT)
A very widespread misconception among beginners is assuming that DISTINCT automatically sorts the results alphabetically or numerically.
DISTINCT guarantees uniqueness, but it provides zero guarantee of row order. Modern database query optimizers often use hash-based aggregation to deduplicate data, which can return rows in arbitrary or unpredictable order.If your application or report requires a specific order, you must explicitly add the ORDER BY clause:
SELECT DISTINCT city
FROM customers;
-- Guaranteed alphabetical sorting (A to Z)
SELECT DISTINCT city
FROM customers
ORDER BY city ASC;
| id | name | city | department | Deduplication Status |
|---|---|---|---|---|
| 1 | Rahul | Mumbai | Sales | ✓ Retained Unique |
| 2 | Priya | Delhi | HR | ✓ Retained Unique |
| 3 | Amit | Mumbai | Sales | ✗ Duplicate Dropped |
| 4 | Sneha | Mumbai | HR | ✓ Retained Unique |
| 5 | Vikram | Delhi | HR | ✗ Duplicate Dropped |
| 6 | Ananya | Pune | Engineering | ✓ Retained Unique |
| 7 | Rohan | Mumbai | Sales | ✗ Duplicate Dropped |
| 8 | Pooja | Delhi | Sales | ✓ Retained Unique |
| city | department |
|---|---|
| Mumbai | Sales |
| Delhi | HR |
| Mumbai | HR |
| Pune | Engineering |
| Delhi | Sales |
DISTINCT With Expressions
DISTINCT is not limited to raw stored columns. It can operate on calculated expressions and string transformations:
SELECT DISTINCT LOWER(city) AS clean_city
FROM customers;
When Should You Use DISTINCT?
- Populating UI dropdown filter options (e.g., all active countries or product categories).
- Auditing categorical values in data cleaning pipelines.
- Finding unique combinations of roles, permissions, or branch locations.
- When querying unique primary keys (e.g.,
user_id). - When querying columns that already have a
UNIQUEdatabase constraint. - As a quick hack to hide duplicate rows caused by incorrect JOIN conditions.
Common DISTINCT Mistakes
DISTINCT is strictly a query-time filter. It does not delete or alter any records stored on disk.
In SELECT DISTINCT a, b FROM table;, DISTINCT applies to the pair (a, b), never just column a alone.
DISTINCT guarantees uniqueness, not order. Always use ORDER BY if you need alphabetical or numerical sorting.
SELECT DISTINCT id, department FROM employees; will return all rows because id is always unique per row.
Practical DISTINCT Exercises
| Task Goal | Target Table | Required SQL Solution | Concept Tested |
|---|---|---|---|
| 1. Find Unique Cities | customers | SELECT DISTINCT city FROM customers; | Single-column deduplication |
| 2. Find Unique Departments | employees | SELECT DISTINCT department FROM employees; | Categorical unique list |
| 3. Find Unique Product Categories | products | SELECT DISTINCT category FROM products; | Product catalog extraction |
| 4. Unique City + Department Combinations | employees | SELECT DISTINCT city, department FROM employees; | Multi-column tuple evaluation |
| 5. Sorted Unique Country List | suppliers | SELECT DISTINCT country FROM suppliers ORDER BY country ASC; | DISTINCT paired with ORDER BY |
DISTINCT Best Practices
- Select Only What Needs Deduplication: Do not include unneeded columns (especially primary keys) in a DISTINCT query.
- Pair With ORDER BY When Order Matters: Never rely on DISTINCT to sort rows alphabetically or numerically.
- Inspect Root Causes Before Adding DISTINCT: If an unexpected duplicate row appears after joining tables, fix the JOIN condition rather than masking it with DISTINCT.
- Be Mindful on Huge Tables: Deduplicating millions of records requires memory buffers for hashing. Indexing the selected columns can significantly accelerate DISTINCT queries.
What You Should Know Now
- ✓What DISTINCT Does: Eliminates duplicate result rows from output
- ✓Single-Column DISTINCT: Keeps unique values for one column
- ✓Multi-Column DISTINCT: Evaluates combined tuples across all columns
- ✓NULL Behavior: Treats multiple NULLs as identical duplicates
- ✓DISTINCT ≠ Sorting: Unordered by default; use ORDER BY if sorted
- ✓Read-Only Query: Never alters physical data stored in database
🎯 Knowledge Check Quiz: SQL DISTINCT
Test your understanding of tuple uniqueness, NULL handling, result predictions, and best practices.