Tutorial Gateway

SQL SELECT DISTINCT Statement

by

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 SELECT DISTINCT Clause

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]

OUTPUT

SQL SELECT DISTINCT Statement 1

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]

OUTPUT
SQL SELECT DISTINCT Statement 2

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

OUTPUT
SQL SELECT DISTINCT Statement 3

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

Placed Under: SQL