DISTINCT
The DISTINCT keyword is used to return only unique values from a column or combination of columns.
When a table contains duplicate values and we want to display each value only once, we can use the DISTINCT keyword.
What is DISTINCT?
DISTINCT removes duplicate values from the result returned by a SELECT query.
It does not delete duplicate records from the actual table. It only removes duplicate values from the query result.
Syntax
SELECT DISTINCT column_name
FROM table_name;
Example Table
| StudentId | StudentName | City | Course |
|---|---|---|---|
| 101 | Rahul | Pune | .NET |
| 102 | Priya | Mumbai | Java |
| 103 | Amit | Pune | Python |
| 104 | Neha | Nashik | .NET |
| 105 | Rohan | Pune | Java |
Without DISTINCT
Suppose we want to display all cities from the Students table.
SELECT City
FROM Students;
The result may contain duplicate city names because multiple students can belong to the same city.
Using DISTINCT
To display each city only once, use DISTINCT.
SELECT DISTINCT City
FROM Students;
The result will contain only unique city names.
DISTINCT with Course
DISTINCT can be applied to any column where we want unique values.
SELECT DISTINCT Course
FROM Students;
This returns each course only once.
DISTINCT with Multiple Columns
DISTINCT can also be used with multiple columns.
SELECT DISTINCT City, Course
FROM Students;
In this case, SQL Server returns unique combinations of City and Course.
DISTINCT with WHERE
DISTINCT can be combined with the WHERE clause to filter records and then return unique values.
SELECT DISTINCT City
FROM Students
WHERE Course = '.NET';
This returns the unique cities of students enrolled in the .NET course.
DISTINCT with ORDER BY
DISTINCT can also be used with ORDER BY to return unique values in a specific order.
SELECT DISTINCT City
FROM Students
ORDER BY City ASC;
This returns unique city names in alphabetical order.
DISTINCT vs GROUP BY
DISTINCT is mainly used to remove duplicate values from a result set.
GROUP BY is generally used when we want to group records and perform aggregate operations such as COUNT, SUM, AVG, MIN and MAX.
SELECT DISTINCT City
FROM Students;
The above query simply returns unique cities.
Important
- DISTINCT returns unique values.
- DISTINCT does not delete duplicate records from the table.
- DISTINCT can be used with one or multiple columns.
- DISTINCT can be combined with WHERE.
- DISTINCT can be combined with ORDER BY.
Summary
In this section, we learned how the DISTINCT keyword is used to return unique values from SQL Server query results. We covered DISTINCT with single columns, multiple columns, WHERE and ORDER BY.