.jpeg)
The DISTINCT keyword in SQL is used to retrieve unique values from a table. It removes duplicate rows from the query result and displays each distinct value only once. It is useful when a table contains repeated data and we need to find the different values stored in a column.
For example, a student table may contain the same city for several students. If we want to find all the cities represented in the table without displaying duplicates, we can use the DISTINCT keyword.
In this article, we will learn about SQL DISTINCT, its syntax, how it works with one or multiple columns, practical examples, and the difference between DISTINCT and GROUP BY.
What Is DISTINCT in SQL?
In SQL, DISTINCT is a keyword that returns only unique rows from the query result. It checks the selected column or combination of columns and removes duplicate results.
For example, suppose an Employees table contains the following data:
| EmployeeID | Name | City |
|---|---|---|
| 1 | Amit | Delhi |
| 2 | Priya | Mumbai |
| 3 | Rahul | Delhi |
| 4 | Neha | Jaipur |
| 5 | Rohit | Mumbai |
If we want to display the different cities in which employees live, we can use DISTINCT with the City column.
Example
SELECT DISTINCT City FROM Employees;
Output:
| City |
|---|
| Delhi |
| Mumbai |
| Jaipur |
Syntax of SQL DISTINCT
The basic syntax of the DISTINCT keyword is:
SELECT DISTINCT column_name FROM table_name;
- SELECT – It specifies the data to retrieve from a table.
- DISTINCT – It removes duplicate rows from the query result.
- column_name – It specifies the column whose values we want to retrieve.
- FROM – It identifies the table from which the data is selected.
- table_name – It specifies the name of the table.
Examples of SQL DISTINCT
Let's understand how the DISTINCT keyword works through practical SQL examples.
1. Using DISTINCT on a Single Column
When we want to retrieve unique values from one column, we place DISTINCT keyword after SELECT.
Example:
SELECT DISTINCT City FROM Employees;
Output:
| City |
|---|
| Delhi |
| Mumbai |
| Jaipur |
2. Using DISTINCT on Multiple Columns
SQL also allows us to use DISTINCT with more than one column. In this case, it removes rows only when the entire combination of selected column values is repeated.
Suppose we have a Students table:
| StudentID | Name | City | Course |
|---|---|---|---|
| 1 | Amit | Delhi | Java |
| 2 | Priya | Mumbai | Python |
| 3 | Rahul | Delhi | Java |
| 4 | Neha | Delhi | Python |
| 5 | Rohit | Mumbai | Python |
Example:
SELECT DISTINCT City, Course FROM Students;
Output:
| City | Course |
|---|---|
| Delhi | Java |
| Mumbai | Python |
| Delhi | Python |
Explanation:
The query considers both City and Course together. The combination Delhi, Java appears twice in the original table, so it is returned only once. However, Delhi, Python and Mumbai, Python are different combinations, so both appear in the result.
3. Using DISTINCT with COUNT()
The COUNT() function returns the number of rows or values that meet the specified conditions. When we combine it with DISTINCT, it counts only unique, non-NULL values.
Example:
SELECT COUNT(DISTINCT City) AS TotalCities FROM Employees;
Output:
| TotalCities |
|---|
| 3 |
Explanation:
The query counts the unique cities in the Employees table. Since the table contains Delhi, Mumbai, and Jaipur, the result is 3.
4. Using DISTINCT with a WHERE Clause
We can combine DISTINCT with the WHERE clause to retrieve unique values that meet a specific condition.
Example:
SELECT DISTINCT City FROM Employees WHERE City = 'Delhi';
Output:
| City |
|---|
| Delhi |
Explanation:
The WHERE clause selects rows where the city is Delhi. The DISTINCT keyword ensures that Delhi appears only once in the result.
5. Using DISTINCT with ORDER BY
The ORDER BY clause sorts the result in ascending or descending order. We can use it with DISTINCT to display unique values in a specific order.
Example:
SELECT DISTINCT City FROM Employees ORDER BY City ASC;
Output:
| City |
|---|
| Delhi |
| Jaipur |
| Mumbai |
Explanation:
The query retrieves unique cities and sorts them alphabetically in ascending order. Use DESC to sort the results in descending order.
6. Using DISTINCT with All Columns
We can use DISTINCT with the asterisk (*) to return unique rows based on all columns in the table.
Example:
SELECT DISTINCT * FROM Employees;
Explanation:
This query removes rows that have identical values in every selected column. If two employees have different employee IDs, their rows remain separate even if their names and cities are the same.
SQL DISTINCT vs GROUP BY
Both DISTINCT and GROUP BY can produce results containing unique combinations of values. However, they are commonly used for different purposes.
| DISTINCT | GROUP BY |
|---|---|
| Removes duplicate rows from the query result. | Groups rows that share the same values in specified columns. |
| Commonly used to retrieve unique values. | Commonly used with aggregate functions such as COUNT(), SUM(), and AVG(). |
| Does not require an aggregate function. | Often used to calculate summary information for each group. |
| Example: SELECT DISTINCT City FROM Employees; | Example: SELECT City, COUNT(*) FROM Employees GROUP BY City; |
Conclusion
The SQL DISTINCT keyword helps us retrieve unique values from a table by removing duplicate rows from the query result. It can be used with a single column, multiple columns, filtering conditions, sorting clauses, and aggregate functions such as COUNT().