In this tutorial, you will learn how to use the SQL Server SELECT DISTINCT clause to retrieve the only distinct values in a specified list of columns.
Estimated reading time: 5 minutes
SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values in other words it is also used to remove duplicates form the result of a SELECT statement.
Syntax
SELECT DISTINCT column1,column2.....
FROM tablename;column1, column2 are the list of columns that are required in result set and tablename is the name of database table.
If you are new to SQL Select Statement Learn more SQL – Full Stack Tutorials Hub.
Notes: –
- When only one column is provided in the
SELECT DISTINCTstatement, the query will return the unique values in that column. - When multiple columns are provided in the
SELECT DISTINCTstatement, the query will return unique combinations of values for the specified columns. - NULL is treated as distinct value by the
SELECT DISTINCTstatement.
Database Query Examples
Following is the Demo Database for example.
In this example there are different type of Hotel Details in the table with combination of Room Type, Room View & Room Staus. Following query will give you the above result.
CREATE DATABASE Hotel
USE Hotel
CREATE TABLE HotelDetails
(
Room_Number int,
Room_type varchar (15),
Room_View varchar (15),
Room_status Varchar (15)
)
INSERT INTO HotelDetails VALUES (1,'Single','PoolView', 'Occupied'),
(2,'Double','PoolView','Unoccupied'),(3,'Single','PoolView', 'Unoccupied'),
(4,'Single','GardenView','Occupied'), (5,'Double','GardenView','Occupied')
SELECT * FROM HotelDetailsWhich will look like below in SQL Screen
Example 1: Example of using SELECT DISTINCT with One Column
When using SELECT DISTINCT with one column, it retrieves unique values from that column. This is particularly useful when you want to eliminate duplicate entries and focus on the distinct values present in the dataset.
SELECT DISTINCT Room_type FROM HotelDetails
In this example we have used SELECT DISTINCT keyword, the statement returns the “Room_type” value from all the records of the “HotelDetails” table.
Example 2: Example of using SELECT DISTINCT with Multiple Columns
When using SELECT DISTINCT with multiple columns, it returns unique combinations of values in the specified columns. This means that each combination of values will appear only once in the result set.
SELECT DISTINCT Room_type, Room_View FROM HotelDetails
In this example we have used SELECT DISTINCT keyword in multiple columns, the statement returns the unique combination of “Room_type, Room_View” from all the records of the “HotelDetails” table. So here we have removed the duplicates and only keep one of each combination of values. It is only when we combine Room_type with Room_View that will give us uniqueness across both columns. In other word, the value of Double is not unique but the combination of values Double and GardenView is unique.
Use of Select Distinct Statement
Differences between SELECT and SELECT DISTINCT
SQL Server Tips and Tricks : Learn in 1 minute
FAQ: DISTINCT in SQL Server
SELECT DISTINCT in SQL Server? he SELECT DISTINCT query in SQL Server is used to remove duplicate rows from a result set and return only unique rows. This is particularly useful when you want to list all unique values in a column or a set of columns.
SELECT DISTINCT affect query performance in SQL Server? Yes, using SELECT DISTINCT can impact query performance, especially on large tables or when using multiple columns. SQL Server has to sort the data to identify and eliminate duplicates, which can be resource intensive. Indexes on the selected columns can help improve performance.
NULL values with SELECT DISTINCT? SQL Server treats all NULL values as equal when using SELECT DISTINCT. If your selected column(s) contain NULL values, the query result will include only one NULL in the list of unique values.
SELECT DISTINCT and using GROUP BY without aggregate functions in SQL Server? Both SELECT DISTINCT and GROUP BY can achieve similar outcomes in terms of returning unique rows. However, GROUP BY is more powerful and flexible, allowing for aggregations (such as COUNT, AVG, MAX) to be applied to grouped rows. SELECT DISTINCT is simpler and directly focuses on eliminating duplicates.