SQL Select Distinct

SELECT DISTINCT used to fetch identical/distinct column values from existing table without duplicate values. By using SELECT DISTINCT command, we can fetch distinct values from existing table in result-set.

Syntax for DISTINCT - 

 SELECT DISTINCT column_name FROM table_name;

Syntax for DISTINCT with multiple columns - 

 SELECT DISTINCT Column1, Column2, Column3, …, ColumnN 
FROM table_name;

Syntax for DISTINCT with aggregate function - 

 SELECT aggregate_function( DISTINCT column_name )FROM Table_name ;
  • column_name - Represents the column name on which DISTINCT applies.

Example

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetching identical values from table.

Requirement – Fetch distinct manager_id from employee_details table. The query was as follows- 

SELECT DISTINCT manager_id FROM employee_details;

By executing above query, we can get output like as shown in below –

manager_id
Clerk
Employee1
Employee2
Employee3
Employee4

Scenario – Fetching identical values from table by using DISTINCT with multiple columns .

Requirement – Fetch distinct employee names from employee_details table. The query was as follows - 

SELECT DISTINCT emp_name, manager_id FROM employee_details;

By executing above query, we can get distinct values of emp_name and the corresponding manager_id.


Note:- The DISTINCT clause applies on the immediate column specified after DISTINCT and the second column onwards have no impact of DISTINCT. The output was as shown in below -

emp_name manager_id
Employee1
Employee2
Employee3 Employee1
Employee4 Employee2
Employee5 Employee3
Employee6 Employee3
Employee7 Employee3
Employee8 Employee4
Employee9 Employee4

Scenario – Fetching identical values from table by using DISTINCT with aggregate function.

Requirement – get the total distinct deprtments count from employee_details table. The query was as follows – 

SELECT COUNT(DISTINCT dept_id) AS 'Total departments' 
FROM employee_details;

By executing above query, we can get distinct departments in employee_details. The output was as shown in below -

Total departments
2