The SQL SELECT DISTINCT Statement is used to retrieve unique records (by removing the duplicates) from the specified Column in the SELECT Statement. In this article we will show you, How to write SELECT DISTINCT Statement in SQL Server.
Syntax of a SQL SELECT DISTINCT
The basic syntax of the SELECT DISTINCT Statement in SQL Server can be written as:
-- SQL Server SELECT DISTINCT Statement SELECT DISTINCT [Column Names] FROM Source WHERE Conditions -- This is Optional
- DISTINCT: Returns Unique Columns
- Columns: It allows us to choose the number of columns from the tables. It may be One or more.
- Source: One or more tables present in the Database. SQL JOINS are used to join multiple table.
We are going to use the below shown data to explain the DISTINCT in SQL Server with example.
SQL DISTINCT on Single Column
In this example, We are going to select the unique records present in the Education Column using SQL DISTINCT.
-- SQL Server SELECT DISTINCT Statement SELECT DISTINCT [Education] FROM [Customer]
SQL DISTINCT on Multiple Columns
When we use the SQL DISTINCT on the multiple columns then the SELECT Statement will write the unique combination of multiple columns instead of unique individual records. In this example, We are going to select the unique records present in the Education Column and Occupation Column.
-- SQL Server SELECT DISTINCT Statement SELECT DISTINCT [Education] ,[Occupation] FROM [Customer]
Although we used the DISTINCT Keyword in the SELECT Statement, from the above screenshot you can observe that, it is returning duplicates because
- Bachelors and Management is Unique Combination
- Bachelors and Professional is Unique Combination
- Partial College and Skilled Manual is Unique Combination
- Partial High School and Clerical is Unique Combination
SQL DISTINCT with WHERE Clause
In this example we will show you, How to use the DISTINCT Keyword in the SELECT Statement along with WHERE Clause. The following statement will return the Distinct Education values whose Yearly Income is greater than or equal to 60000
-- SQL Server SELECT DISTINCT Statement SELECT DISTINCT [Education] FROM [Customer] WHERE [YearlyIncome] >=60000
Although there are 5 distinct records for the Education Column, Partial High School record does not meet the Where condition. That’s why above screenshot is showing 4 records instead of 5.
NOTE: The SQL DISTINCT consider the NULL records as a valid unique record so, Please use any Not Null function (NOT NULL) functions to remove NULLS.
Thank You for Visiting Our Blog