Using SELECT DISTINCT in SQL: A Comprehensive Guide

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

• SELECT DISTINCT returns only distinct (different) values.
• SELECT DISTINCT eliminates duplicate records from the results.
• DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.
• DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.

The general syntax is:

Syntax:

SELECT DISTINCT column1, column2, ...columnN
FROM table_name;

Can be used with COUNT and other aggregates

Syntax:

SELECT COUNT (DISTINCT columnName)
  FROM tableName

Example:

If you don't have table please create below table and practice it.

-- My database name is SQLExamples
USE SQLExamples

-- Create this below Employee table
DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

-- Insert data inside table
INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Code: How many Unique EmpDept are present

SELECT DISTINCT EmpDept
  FROM Employee

Output:

The above code will produce the following result-

EmpDept
ADMIN
IT
ITIS

Code: How many unique dept are present

  SELECT COUNT (DISTINCT EmpDept) as DeptCount
  FROM Employee

Output:

The above code will produce the following result-

DeptCount
3