SQL DISTINCT Keyword

The SQL DISTINCT keyword is used to return only unique values from a table. When a column contains duplicate data, DISTINCT helps remove repeated values and displays only one occurrence of each value in the result set. It is one of the most commonly used SQL keywords when analyzing data and generating reports.

In real-world databases, duplicate values often exist because multiple records may share the same department, city, category, course, or product type. If all records are displayed, the result may become difficult to analyze. The DISTINCT keyword simplifies the output by showing only unique entries.

Understanding DISTINCT is important for database developers, analysts, students, and professionals because it improves data readability and helps generate meaningful reports.

What is the DISTINCT Keyword?

The DISTINCT keyword removes duplicate rows from the result of a SELECT statement. It ensures that only unique values are displayed in the query output.

Without DISTINCT, SQL returns all matching rows, including repeated values. By using DISTINCT, duplicate values are automatically filtered out.

Why Use DISTINCT?

Removes duplicate values from query results.
Improves data readability.
Creates cleaner reports.
Helps identify unique records.
Useful in analytics and business reporting.
Reduces unnecessary duplicate information.
Supports data exploration and analysis.

Basic Syntax of DISTINCT

SELECT DISTINCT column_name
FROM table_name;

The query returns only unique values from the specified column.

Sample Student Table

StudentID	StudentName	Course
101	Rahul	BCA
102	Priya	BCA
103	Amit	MCA
104	Neha	B.Tech
105	Rohan	MCA

DISTINCT with a Single Column

The most common use of DISTINCT is with a single column.

SELECT DISTINCT Course
FROM Student;

Output

Course
BCA
MCA
B.Tech

Although multiple students belong to BCA and MCA, DISTINCT displays each course only once.

SELECT Without DISTINCT

Let's compare the result without using DISTINCT.

SELECT Course
FROM Student;

Output

Course
BCA
BCA
MCA
B.Tech
MCA

Duplicate values are visible because DISTINCT was not used.

DISTINCT with Multiple Columns

DISTINCT can also work with multiple columns. In this case, SQL checks the uniqueness of the combined column values.

SELECT DISTINCT Course, StudentName
FROM Student;

The combination of Course and StudentName must be unique for a row to appear only once.

Understanding Combined Uniqueness

When multiple columns are specified, SQL does not check uniqueness individually. Instead, it checks the complete combination of values.

Course	StudentName
BCA	Rahul
BCA	Priya

These rows are considered unique because the combinations are different.

DISTINCT with Numeric Values

DISTINCT works with numbers just as effectively as it works with text values.

Marks Table

Marks
80
90
80
95

SELECT DISTINCT Marks
FROM StudentMarks;

Output

Marks
80
90
95

DISTINCT with Text Values

Text-based columns often contain repeated values. DISTINCT helps identify all unique text entries.

SELECT DISTINCT City
FROM Customers;

The query displays all cities where customers are located without repeating city names.

DISTINCT with NULL Values

A common interview question involves DISTINCT and NULL values.

When a column contains multiple NULL values, DISTINCT treats them as a single unique value.

Example

City
Delhi
NULL
NULL
Mumbai

SELECT DISTINCT City
FROM Customers;

Only one NULL value appears in the result set.

Practical Example: Employee Database

Suppose an organization wants to know all departments present in the company.

SELECT DISTINCT Department
FROM Employee;

The result displays each department only once, regardless of how many employees belong to it.

Practical Example: E-Commerce Website

An online store may need a list of unique product categories.

SELECT DISTINCT Category
FROM Products;

This helps create category filters for customers.

Practical Example: College Management System

A college administrator may want to know all available courses.

SELECT DISTINCT Course
FROM Student;

This query quickly generates a list of unique courses.

Real-World Applications of DISTINCT

Unique customer locations.
Available product categories.
Department-wise reporting.
Unique course listings.
Country and city reports.
Business analytics dashboards.
Data cleansing and validation.

Best Practices for Using DISTINCT

Use DISTINCT only when necessary.
Understand the impact on performance.
Combine with proper filtering conditions.
Use indexes on frequently queried columns.
Verify whether duplicates actually need removal.
Keep queries simple and readable.

Common Mistakes to Avoid

Using DISTINCT unnecessarily.
Assuming it modifies actual table data.
Confusing DISTINCT with GROUP BY.
Ignoring performance impacts on large datasets.
Misunderstanding how multiple-column DISTINCT works.

DISTINCT vs GROUP BY

DISTINCT	GROUP BY
Removes duplicates.	Groups records.
Simple unique value retrieval.	Often used with aggregate functions.
Easier for basic duplicate removal.	More powerful for data analysis.

DISTINCT with WHERE Clause

The DISTINCT keyword can be combined with the WHERE clause to return unique values that satisfy a specific condition. This combination is frequently used in real-world applications where only selected records need to be analyzed.

Example

SELECT DISTINCT Department
FROM Employee
WHERE Salary > 50000;

This query returns only the unique departments where employees have salaries greater than 50,000.

DISTINCT with ORDER BY

After removing duplicate values, SQL can sort the remaining results using the ORDER BY clause.

Example

SELECT DISTINCT City
FROM Customers
ORDER BY City ASC;

The result displays unique city names in alphabetical order.

DISTINCT with Descending Order

SELECT DISTINCT Category
FROM Products
ORDER BY Category DESC;

The categories are displayed in reverse alphabetical order.

DISTINCT with GROUP BY

Many beginners ask whether DISTINCT and GROUP BY perform similar tasks. While both can return unique values, GROUP BY is mainly designed for grouping records and performing calculations.

Using DISTINCT

SELECT DISTINCT Department
FROM Employee;

Using GROUP BY

SELECT Department
FROM Employee
GROUP BY Department;

Both queries may produce similar results, but GROUP BY becomes more useful when aggregate functions are involved.

DISTINCT with COUNT()

One of the most popular uses of DISTINCT is with the COUNT() function. It helps count only unique values instead of all values.

Example

SELECT COUNT(DISTINCT Department)
AS TotalDepartments

FROM Employee;

This query counts only unique departments.

COUNT() Without DISTINCT

SELECT COUNT(Department)
FROM Employee;

This counts every department value, including duplicates.

COUNT(DISTINCT) Example

Department
IT
IT
HR
Finance

COUNT(Department) returns 4 while COUNT(DISTINCT Department) returns 3.

DISTINCT with Multiple Columns

DISTINCT can remove duplicate combinations when more than one column is selected.

Example

SELECT DISTINCT Department,
Designation

FROM Employee;

SQL checks uniqueness based on the complete Department-Designation combination.

DISTINCT with Aggregate Functions

DISTINCT can be used inside aggregate functions to eliminate duplicate values before calculations are performed.

SUM(DISTINCT)

SELECT SUM(DISTINCT Salary)
FROM Employee;

Duplicate salary values are counted only once before calculating the total.

AVG(DISTINCT)

SELECT AVG(DISTINCT Salary)
FROM Employee;

Only unique salary values contribute to the average calculation.

DISTINCT with JOIN

When multiple tables are joined, duplicate rows often appear in query results. DISTINCT helps remove unnecessary duplicates.

Example

SELECT DISTINCT Department.DepartmentName

FROM Department

INNER JOIN Employee

ON Department.DepartmentID =
Employee.DepartmentID;

The query returns each department name only once even if multiple employees belong to the same department.

DISTINCT with LEFT JOIN

SELECT DISTINCT DepartmentName

FROM Department

LEFT JOIN Employee

ON Department.DepartmentID =
Employee.DepartmentID;

This query returns a unique list of departments regardless of whether employees exist in those departments.

DISTINCT Across Multiple Tables

DISTINCT is especially useful in relational databases where data is distributed across several tables.

SELECT DISTINCT Customer.CustomerName

FROM Customer

INNER JOIN Orders

ON Customer.CustomerID =
Orders.CustomerID;

The query shows customers who have placed orders without displaying duplicate customer names.

Advanced Example: Online Shopping System

Suppose an e-commerce platform wants a list of unique product categories purchased by customers.

SELECT DISTINCT Category
FROM Orders;

This helps generate category-based analytics reports.

Advanced Example: University Database

SELECT DISTINCT Course
FROM Student;

The administration can quickly identify all courses currently offered.

Advanced Example: Banking System

SELECT DISTINCT BranchName
FROM Accounts;

The query provides a unique list of bank branches.

Advanced Example: Hospital Management System

SELECT DISTINCT Department
FROM Patients;

Hospital administrators can identify all departments handling patients.

Performance Considerations

Although DISTINCT is simple to use, it may require additional processing on large datasets because SQL must identify and eliminate duplicate values.

The performance impact becomes more noticeable when:

Large tables are involved.
Multiple columns are used.
Complex JOIN operations exist.
No indexes are available.

Optimization Tips

Create indexes on frequently searched columns.
Use WHERE to reduce rows before applying DISTINCT.
Select only required columns.
Avoid unnecessary DISTINCT usage.
Analyze execution plans for large queries.
Optimize JOIN conditions.

When Should DISTINCT Be Used?

Finding unique cities.
Identifying unique departments.
Creating category reports.
Removing duplicate query results.
Counting unique values.
Generating dashboard summaries.

When DISTINCT Should Be Avoided?

When duplicates are actually needed.
When performance is critical.
When GROUP BY provides a better solution.
When duplicate data should be fixed in the database itself.

Advantages of DISTINCT

Easy to understand.
Removes duplicate records efficiently.
Improves report clarity.
Works with aggregate functions.
Useful for business intelligence reporting.
Supports data analysis tasks.
Reduces repetitive information.

Limitations of DISTINCT

May reduce performance on large datasets.
Does not permanently remove duplicates from tables.
Requires additional processing.
Can become expensive with complex joins.

SQL DISTINCT Interview Questions and Answers

1. What is DISTINCT in SQL?

DISTINCT returns only unique values from a query result.

2. Why is DISTINCT used?

It removes duplicate values from output.

3. Does DISTINCT change table data?

No, it only affects query results.

4. Can DISTINCT be used with multiple columns?

Yes.

5. Can DISTINCT be combined with WHERE?

Yes.

6. Can DISTINCT be combined with ORDER BY?

Yes.

7. Can DISTINCT be combined with JOIN?

Yes.

8. What is COUNT(DISTINCT)?

It counts only unique values.

9. Does DISTINCT remove NULL values?

No, but multiple NULL values appear only once.

10. Can DISTINCT work with numbers?

Yes.

11. Can DISTINCT work with text values?

Yes.

12. What is the syntax of DISTINCT?

SELECT DISTINCT column_name
FROM table_name;

13. Does DISTINCT improve readability?

Yes.

14. Is DISTINCT useful in reports?

Yes.

15. Can DISTINCT replace GROUP BY?

Not always.

16. What happens if all values are unique?

The output remains unchanged.

17. Can DISTINCT affect performance?

Yes, especially on large tables.

18. Can DISTINCT be used in subqueries?

Yes.

19. What is DISTINCT mainly used for?

Removing duplicate results.

20. Can DISTINCT work with aggregate functions?

Yes.

21. What is SUM(DISTINCT)?

It sums only unique values.

22. What is AVG(DISTINCT)?

It averages only unique values.

23. Is DISTINCT supported by major databases?

Yes.

24. Can DISTINCT be used with aliases?

Yes.

25. Why should SQL learners understand DISTINCT?

It is essential for creating clean and meaningful reports.

Conclusion

The SQL DISTINCT keyword is an important tool for eliminating duplicate values and generating cleaner query results. It helps developers, analysts, and database administrators work with unique information efficiently. DISTINCT supports multiple columns, aggregate functions, JOIN operations, filtering conditions, and sorting operations.

By understanding how DISTINCT works and when to use it, database professionals can improve report quality, enhance data analysis, and create more effective SQL queries. Mastering DISTINCT is an essential step toward becoming proficient in SQL database management and reporting.

← Previous: HAVING Clause Next: UPDATE Statement →