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

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.

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.

  • When only one column is provided in the SELECT DISTINCT statement, the query will return the unique values in that column.
  • When multiple columns are provided in the SELECT DISTINCT statement, the query will return unique combinations of values for the specified columns.
  • NULL is treated as distinct value by the SELECT DISTINCT statement.

Following is the Demo Database for example.

Select Distinct ex. 1

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.


USE Hotel 

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 HotelDetails

Which 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

Distinct Values – SELECT DISTINCT retrieves unique values from a specified column.
Data Deduplication – It eliminates duplicate rows from the result set of a query.
Use Case Example – Effective when it’s necessary to display only distinct values.

Differences between SELECT and SELECT DISTINCT


What is the purpose of using 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.

Can 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.

How does SQL Server handle 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.

Is there a difference between 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.

Scroll to Top