SQL Topics
DISTINCT Keyword
title: DISTINCT Keyword
In real-world databases, duplicate values are very common. Multiple customers may belong to the same city, many employees may work in the same department, and thousands of orders may share identical product categories.
When analyzing data, displaying duplicate values repeatedly often makes reports larger and harder to understand. Instead, businesses frequently need only unique values.
For example:
- A company wants a list of unique departments.
- A school wants all distinct classes.
- An e-commerce platform wants unique product categories.
- A bank wants a list of unique branch locations.
To solve this problem, SQL provides the DISTINCT Keyword.
The DISTINCT keyword removes duplicate values from query results and returns only unique records. It is commonly used in reporting, analytics, and data exploration where identifying unique values is important.
What is DISTINCT?
The DISTINCT keyword is used to return only unique values from a query result.
Example:
SELECT DISTINCT City
FROM Customers;Without DISTINCT:
Delhi
Mumbai
Delhi
Kolkata
MumbaiWith DISTINCT:
Delhi
Mumbai
KolkataDuplicate values are removed automatically.
Why is DISTINCT Important?
Consider a Customers table:
| CustomerID | Name | City |
|---|---|---|
| 1 | Rahul | Delhi |
| 2 | Priya | Mumbai |
| 3 | Amit | Delhi |
| 4 | Neha | Mumbai |
| 5 | Rohan | Kolkata |
If we run:
SELECT City
FROM Customers;Output:
Delhi
Mumbai
Delhi
Mumbai
KolkataThe same city appears multiple times.
Using DISTINCT:
SELECT DISTINCT City
FROM Customers;Output:
Delhi
Mumbai
KolkataThis produces cleaner and more meaningful results.
Basic DISTINCT Syntax
The general syntax is:
SELECT DISTINCT ColumnName
FROM TableName;Example:
SELECT DISTINCT Department
FROM Employees;This returns only unique departments.
Understanding the Syntax
Example:
SELECT DISTINCT City
FROM Customers;SELECT
Retrieves data.
DISTINCT
Removes duplicate values.
City
Column being analyzed.
FROM
Specifies the source table.
Customers
Table containing the data.
Creating a Sample Table
Example:
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50)
);Insert records:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT'),
(2, 'Priya', 'HR'),
(3, 'Amit', 'IT'),
(4, 'Neha', 'Finance'),
(5, 'Rohan', 'HR');Current data:
| EmployeeID | Name | Department |
|---|---|---|
| 1 | Rahul | IT |
| 2 | Priya | HR |
| 3 | Amit | IT |
| 4 | Neha | Finance |
| 5 | Rohan | HR |
DISTINCT on a Single Column
Query:
SELECT DISTINCT Department
FROM Employees;Result:
| Department |
|---|
| ------------ |
| IT |
| HR |
| Finance |
Each department appears only once.
DISTINCT on Multiple Columns
DISTINCT can work on multiple columns together.
Example:
SELECT DISTINCT Department, Name
FROM Employees;SQL checks the combination of both columns.
Result:
| Department | Name |
|---|---|
| IT | Rahul |
| HR | Priya |
| IT | Amit |
| Finance | Neha |
| HR | Rohan |
Since every combination is unique, all rows are returned.
How DISTINCT Works
SQL processes DISTINCT after retrieving data.
Example:
Original Data
ā
Find Duplicates
ā
Remove Duplicates
ā
Return Unique ValuesOnly unique records remain in the final result.
DISTINCT with Numbers
Example:
Products table:
| ProductID | Price |
|---|---|
| 1 | 500 |
| 2 | 1000 |
| 3 | 500 |
| 4 | 1500 |
Query:
SELECT DISTINCT Price
FROM Products;Result:
| Price |
|---|
| -------- |
| 500 |
| 1000 |
| 1500 |
Duplicate prices are removed.
DISTINCT with Text Data
Example:
SELECT DISTINCT City
FROM Customers;Result:
Delhi
Mumbai
KolkataOnly unique city names are displayed.
DISTINCT with NULL Values
Suppose:
| ID | |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | test@gmail.com |
Query:
SELECT DISTINCT Email
FROM Users;Result:
NULL
test@gmail.comMultiple NULL values are treated as a single unique value.
DISTINCT with ORDER BY
DISTINCT is often combined with ORDER BY.
Example:
SELECT DISTINCT City
FROM Customers
ORDER BY City;Result:
Delhi
Kolkata
MumbaiUnique values are sorted alphabetically.
DISTINCT with WHERE
Filtering and duplicate removal are commonly used together.
Example:
SELECT DISTINCT Department
FROM Employees
WHERE Salary > 50000;Process:
WHERE ā Filter Records
ā
DISTINCT ā Remove DuplicatesOnly unique departments meeting the condition are returned.
DISTINCT with COUNT()
A common interview topic is counting unique values.
Example:
SELECT COUNT(DISTINCT Department)
FROM Employees;Result:
3This counts unique departments rather than total records.
Real-World Example
Imagine an e-commerce website.
Products table:
| ProductID | Category |
|---|---|
| 1 | Electronics |
| 2 | Electronics |
| 3 | Clothing |
| 4 | Books |
Query:
SELECT DISTINCT Category
FROM Products;Result:
| Category |
|---|
| ----------- |
| Electronics |
| Clothing |
| Books |
Useful for building category filters and reports.
DISTINCT vs GROUP BY
Many beginners confuse DISTINCT and GROUP BY.
DISTINCT
Used to remove duplicates.
Example:
SELECT DISTINCT Department
FROM Employees;GROUP BY
Used for aggregation.
Example:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department;Comparison:
| Feature | DISTINCT | GROUP BY |
|---|---|---|
| Removes Duplicates | Yes | Yes |
| Performs Aggregation | No | Yes |
| Simpler Syntax | Yes | No |
| Used for Reporting | Limited | Extensive |
Common Errors
Expecting DISTINCT to Remove Partial Duplicates
Example:
SELECT DISTINCT Department, Name
FROM Employees;SQL evaluates both columns together.
Using DISTINCT Unnecessarily
Wrong:
SELECT DISTINCT EmployeeID
FROM Employees;If EmployeeID is already unique, DISTINCT is unnecessary.
Confusing DISTINCT with GROUP BY
DISTINCT removes duplicates.
GROUP BY creates groups for aggregation.
Best Practices
Use DISTINCT Only When Needed
Duplicate removal requires additional processing.
Avoid unnecessary usage.
Combine with ORDER BY
Produces cleaner reports.
Example:
SELECT DISTINCT City
FROM Customers
ORDER BY City;Use COUNT(DISTINCT)
For counting unique values.
Example:
SELECT COUNT(DISTINCT City)
FROM Customers;Understand Multi-Column Behavior
DISTINCT evaluates the complete row or column combination.
Common Interview Questions
What is DISTINCT used for?
DISTINCT removes duplicate values and returns only unique records.
Can DISTINCT work on multiple columns?
Yes.
Example:
SELECT DISTINCT Department, Name
FROM Employees;Does DISTINCT remove duplicate NULL values?
Yes.
Multiple NULL values appear only once.
What is COUNT(DISTINCT)?
It counts unique values instead of total rows.
Summary
The DISTINCT keyword is a powerful SQL feature used to eliminate duplicate values and return unique records. It helps create cleaner reports, improve data analysis, and identify unique values efficiently.
In this lesson, you learned:
- What DISTINCT is
- Why DISTINCT is important
- Basic syntax
- Single-column DISTINCT
- Multi-column DISTINCT
- DISTINCT with NULL values
- DISTINCT with ORDER BY
- DISTINCT with WHERE
- COUNT(DISTINCT)
- DISTINCT vs GROUP BY
- Best practices
Understanding DISTINCT is essential because identifying unique values is a common requirement in data analysis and reporting.
Next Step
Continue to the next lesson:
LIMIT Clause ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for DISTINCT Keyword.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, distinct
Related SQL Topics