When working with a database, the same information may appear in many different records. For example, hundreds of students may belong to the same course, many employees may work in the same department, and thousands of customers may live in the same city. If we simply display a column from such a table, repeated values will normally appear in the result.
The SQL DISTINCT keyword is used when we want to see only unique values or unique combinations of values in a query result. It is commonly used while exploring data, preparing reports, identifying categories, and calculating the number of different values stored in a column.
An important point to remember is that DISTINCT does not remove records from the database. It only changes the result returned by the SELECT query. The original table remains unchanged.
DISTINCT is a keyword used with the SELECT statement to eliminate duplicate rows from the result set. SQL examines the values selected by the query and returns each identical combination only once.
For example, suppose an employee table contains the following departments:
IT HR IT Finance HR IT
A normal SELECT query returns all six values. If we only need a list of departments, repeated values are unnecessary. DISTINCT can produce:
IT HR Finance
Therefore, DISTINCT is particularly useful when the requirement is to obtain a list of different values rather than every individual record.
SELECT DISTINCT column_name FROM table_name;
Here, column_name represents the field from which unique values are required, while table_name represents the table containing the data.
SELECT DISTINCT Department FROM Employee;
This query returns each department only once.
DISTINCT is useful whenever repeated values do not provide additional information in the required report.
The following table will be used for several examples in this tutorial.
| StudentID | StudentName | Course | City | Marks |
|---|---|---|---|---|
| 101 | Rahul | BCA | Delhi | 82 |
| 102 | Priya | BCA | Meerut | 91 |
| 103 | Amit | MCA | Delhi | 76 |
| 104 | Neha | B.Tech | Jaipur | 88 |
| 105 | Rohan | MCA | Meerut | 84 |
| 106 | Simran | BCA | Delhi | 79 |
First, consider a normal SELECT query:
SELECT Course FROM Student;
The result can contain:
| Course |
|---|
| BCA |
| BCA |
| MCA |
| B.Tech |
| MCA |
| BCA |
The repeated BCA and MCA values are expected because the query returns one value for every matching row.
If the requirement is to display each course only once, DISTINCT can be added to the SELECT statement.
SELECT DISTINCT Course FROM Student;
The result will contain the unique courses:
| Course |
|---|
| BCA |
| MCA |
| B.Tech |
The database does not modify the Student table. It simply removes repeated values from this particular result.
The same technique can be used to find the different cities represented in the student table.
SELECT DISTINCT City FROM Student;
The result contains Delhi, Meerut, and Jaipur, with each city appearing once.
This type of query is useful when building filters, reports, dropdown lists, and data-analysis screens.
DISTINCT is not restricted to character data. It can also be used with numbers.
SELECT DISTINCT Marks FROM Student;
If several students have the same marks, that mark will appear only once in the result.
For example, if the Marks column contains:
80 90 80 75 90
the DISTINCT result will contain:
80 90 75
One of the most important concepts is that DISTINCT considers all selected columns together when more than one column is specified.
SELECT DISTINCT Course, City FROM Student;
Here SQL does not make Course unique separately and City unique separately. Instead, it checks the complete Course + City combination.
For example:
| Course | City |
|---|---|
| BCA | Delhi |
| BCA | Meerut |
| MCA | Delhi |
| MCA | Meerut |
Two rows with the same course can both remain in the result if their cities are different.
Consider these rows:
BCA Delhi BCA Delhi BCA Meerut
After:
SELECT DISTINCT Course, City FROM Student;
the first two rows are considered duplicates because both selected values are identical. The BCA-Meerut combination is different, so it remains.
DISTINCT can be combined with WHERE when only a particular subset of records should be considered.
SELECT DISTINCT Course FROM Student WHERE Marks >= 80;
The WHERE condition first limits the rows considered by the query. DISTINCT then ensures that the resulting courses are not repeated.
This is useful when a report needs unique categories satisfying a particular condition.
ORDER BY can be used to arrange the unique values in ascending or descending order.
SELECT DISTINCT City FROM Student ORDER BY City ASC;
For descending order:
SELECT DISTINCT City FROM Student ORDER BY City DESC;
Using ORDER BY makes a DISTINCT result easier to read, particularly when the output contains many unique values.
Sometimes we do not need the actual unique values. Instead, we need to know how many different values exist. SQL provides COUNT(DISTINCT ...) for this purpose.
SELECT COUNT(DISTINCT Course) AS TotalCourses FROM Student;
This query counts the different courses represented in the Student table.
For the sample data, the result would be 3 because the courses are BCA, MCA, and B.Tech.
| COUNT() | COUNT(DISTINCT) |
|---|---|
| Counts qualifying values or rows depending on the expression. | Counts unique non-NULL values of the expression. |
| Repeated values can contribute multiple times. | Repeated values contribute only once. |
| Useful when total occurrences are required. | Useful when the number of different values is required. |
SELECT COUNT(Course) AS TotalCourseValues FROM Student;
This counts the non-NULL Course values.
SELECT COUNT(DISTINCT Course) AS DifferentCourses FROM Student;
This counts the different courses.
DISTINCT can also be supplied to certain aggregate functions when the calculation should use unique input values.
SELECT SUM(DISTINCT Salary) AS UniqueSalaryTotal FROM Employee;
Each different salary value is considered once for the calculation.
SELECT AVG(DISTINCT Salary) AS UniqueSalaryAverage FROM Employee;
The calculation is based on distinct salary values rather than every occurrence.
The usefulness of these expressions depends on the actual reporting requirement. DISTINCT should not be added to an aggregate simply because duplicate values exist.
NULL represents an unknown or missing value in SQL. When DISTINCT is applied to a column containing several NULL entries, the result contains a single NULL entry for that group of identical NULL values.
SELECT DISTINCT City FROM Student;
If several students have a missing City value, DISTINCT does not display a separate NULL row for every student.
For counting, it is important to remember that COUNT(DISTINCT column_name) counts distinct non-NULL values.
DISTINCT and GROUP BY can sometimes produce similar-looking results, but their purpose is not exactly the same.
| DISTINCT | GROUP BY |
|---|---|
| Returns unique result combinations. | Creates groups of rows. |
| Very convenient for a unique list. | Designed for grouped analysis. |
| Does not require an aggregate function. | Commonly used with aggregate functions. |
| Useful for removing repeated output values. | Useful for calculations such as COUNT, SUM and AVG per group. |
SELECT DISTINCT Department FROM Employee;
SELECT Department, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Department;
If the requirement is simply a list of departments, DISTINCT communicates the intention clearly. If the requirement involves department-wise calculations, GROUP BY is generally the appropriate approach.
JOIN operations can produce repeated values when one record in one table matches several records in another table. DISTINCT can be useful when the final report needs only unique values.
SELECT DISTINCT d.DepartmentName FROM Department AS d INNER JOIN Employee AS e ON d.DepartmentID = e.DepartmentID;
If a department has many employees, the JOIN may initially produce the department name many times. DISTINCT returns the department name once in the final result.
However, DISTINCT should not be used automatically whenever duplicate rows appear after a JOIN. Unexpected duplication may indicate an incorrect JOIN condition or an incorrect understanding of the table relationship.
Suppose an online shopping system stores customers in one table and their orders in another. A customer can place multiple orders.
SELECT DISTINCT c.CustomerName FROM Customer AS c INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID;
The query returns customers who have orders, with each customer appearing once even if that customer has placed many orders.
DISTINCT can also be combined with LEFT JOIN when the report should include records from the left table regardless of whether matching records exist on the right.
SELECT DISTINCT d.DepartmentName FROM Department AS d LEFT JOIN Employee AS e ON d.DepartmentID = e.DepartmentID;
Each department is returned once. The LEFT JOIN also ensures that departments without employees are not automatically removed from the result.
A college may want to display the different courses currently represented in its student database.
SELECT DISTINCT Course FROM Student ORDER BY Course;
This provides a simple course list without repeating a course for every student.
An organization may have hundreds of employees but only a small number of departments. To obtain the department list:
SELECT DISTINCT Department FROM Employee ORDER BY Department;
This can be useful when preparing an HR report or creating a department selection list.
An online store may want to know which product categories have been used in its order records.
SELECT DISTINCT Category FROM Orders ORDER BY Category;
Repeated categories from different orders appear only once in the result.
A bank may maintain customer accounts across multiple branches. A simple DISTINCT query can identify the different branches represented in an account table.
SELECT DISTINCT BranchName FROM Accounts ORDER BY BranchName;
This provides a unique branch list without modifying account records.
A hospital database may contain many patient records associated with different medical departments.
SELECT DISTINCT Department FROM Patients ORDER BY Department;
The result gives the departments represented in the patient data.
DISTINCT is appropriate when uniqueness is an actual requirement of the result.
DISTINCT should not be treated as a general-purpose solution for every duplicate result.
If a query unexpectedly returns duplicate rows, first examine the query and database relationships. The problem may be caused by an incorrect JOIN condition, missing filtering condition, or misunderstanding of the data.
Adding DISTINCT may hide that underlying problem while making the query more expensive.
DISTINCT does not delete anything from the table. It only changes the result returned by SELECT.
When multiple columns are selected, SQL evaluates the complete combination of selected values.
Unexpected duplicate rows should be investigated instead of automatically being removed with DISTINCT.
If every row is already unique and all rows are required, DISTINCT provides no useful benefit.
DISTINCT does not permanently clean duplicate data stored in a database.
To produce a unique result, the database engine may need to perform additional processing. Depending on the database system and execution plan, this may involve sorting, hashing, or another duplicate-elimination strategy.
For a small table, this cost may be insignificant. On a large dataset containing millions of rows, however, unnecessary duplicate elimination can affect query performance.
DISTINCT can be combined with several SQL features in the same query.
SELECT DISTINCT d.DepartmentName FROM Department AS d INNER JOIN Employee AS e ON d.DepartmentID = e.DepartmentID WHERE e.Salary >= 60000 ORDER BY d.DepartmentName;
The query identifies departments having at least one employee whose salary satisfies the condition and returns each qualifying department once.
One useful application of DISTINCT is understanding an unfamiliar database. Before creating a complicated report, a developer can inspect the different values present in important columns.
SELECT DISTINCT Status FROM Orders;
This can reveal values such as Pending, Shipped, Delivered, or Cancelled. Such exploratory queries help developers understand the actual data before writing larger SQL statements.
It is important to distinguish between duplicate output and duplicate data.
Suppose a Student table contains three students enrolled in BCA. The value BCA appears three times because each student has a separate record. These are not necessarily duplicate records.
SELECT DISTINCT Course FROM Student;
This query hides repeated course values in the output, but it does not remove the three student records.
Therefore, DISTINCT should be understood as a result-set operation, not a data-deletion operation.
Assume the following table:
| ID | City |
|---|---|
| 1 | Delhi |
| 2 | Delhi |
| 3 | Jaipur |
| 4 | Delhi |
| 5 | Jaipur |
Normal query:
SELECT City FROM Customer;
Possible result:
Delhi Delhi Jaipur Delhi Jaipur
Using DISTINCT:
SELECT DISTINCT City FROM Customer;
Result:
Delhi Jaipur
This simple example captures the main purpose of DISTINCT.
DISTINCT is used with SELECT to return unique result rows and eliminate repeated combinations of the selected values.
No. DISTINCT only affects the result of the SELECT query. The original table is not changed.
SELECT DISTINCT column_name FROM table_name;
Yes. SQL checks the complete combination of the selected columns.
It is used to count distinct non-NULL values of an expression.
Yes. WHERE can restrict the rows and DISTINCT can remove repeated values from the selected result.
Yes. ORDER BY can sort the resulting unique rows.
Yes. It can be useful when the required output from a correctly designed JOIN should contain unique values.
DISTINCT returns unique result combinations, whereas GROUP BY creates groups and is commonly used for grouped aggregate calculations.
No. The original data remains unchanged.
Yes. DISTINCT can be applied to numeric, text, date, and other supported expressions.
Multiple NULL values in the selected result are represented by a single NULL value when DISTINCT is applied.
Yes. COUNT(DISTINCT column_name) is widely used to count unique non-NULL values.
Yes, many SQL systems support expressions such as SUM(DISTINCT column_name).
Yes, SQL systems that support the syntax can calculate AVG(DISTINCT column_name).
No. Duplicate elimination can require additional processing, so DISTINCT should be used only when unique results are actually required.
A one-to-many or many-to-many relationship can naturally produce multiple matching rows. An incorrect JOIN condition can also create unexpected duplicates.
No. First determine why the duplicate result is occurring. DISTINCT should be used when the final report genuinely requires unique results.
Yes. Developers can use it to discover the different values stored in a column.
It provides a simple way to obtain unique result information and is useful in reporting, analysis, data exploration, and unique-value counting.
| Concept | Explanation |
|---|---|
| DISTINCT | Returns unique result rows. |
| Single column | Removes repeated values from that column's result. |
| Multiple columns | Checks the complete selected combination. |
| COUNT(DISTINCT) | Counts distinct non-NULL values. |
| WHERE | Restricts rows before the final result is produced. |
| ORDER BY | Sorts the result. |
| GROUP BY | Creates groups for grouped calculations. |
| JOIN | Can be combined with DISTINCT when a unique result is required. |
| Original table | DISTINCT does not modify stored records. |
The SQL DISTINCT keyword is a simple but important feature for working with database results. It allows developers and students to obtain unique values without changing the records stored in the underlying table. This makes it useful for reports, data exploration, filtering interfaces, category lists, and analytical queries.
The most important concept is to understand what SQL considers a duplicate. With one selected column, repeated values are removed from the output. With multiple columns, SQL evaluates the complete combination of selected values. DISTINCT can also be combined with COUNT, WHERE, ORDER BY, and JOIN operations to solve practical database problems.
For efficient SQL development, DISTINCT should be used because the result genuinely needs to be unique—not simply because a query happens to return unexpected duplicates. Understanding the reason behind duplicate results leads to better queries, clearer reports, and more maintainable database applications.