SQL Select Distinct

The SQL DISTINCT query is used to fetch unique values from the tables using the SELECT statement in the SQL.

There may be a situation that arises when you want to fetch the unique values from the tables, but duplicate values are also present inside the table. In such case, you should use the SELECT DISTINCT query to eliminate duplicate values and fetch unique values from the table.

Syntax of SELECT DISTINCT statement:

SELECT DISTINCT columnname1, columnname2 FROM table_name;

Consider the existing employee's tables which have the following records:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING	4
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Let's understand the SELECT DISTINCT with the help of an example.

Example 1: Write a query to fetch employee details whose employee salary is greater than 55000 without using the DISTINCT keyword.

SELECT * FROM EMPLOYEES WHERE SALARY > 55000;

In the above statement, we have fetched the employee details from the employee's table whose employee salary is greater than 55000, including the duplicate values.

The output for the following statement:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

The above result shows we have fetched the employees details, including duplicate values.

Example 2: Write a query to display employee salary from employees table whose employee salary is greater than 55000 using DISTINCT keyword.

SELECT DISTINCT (SALARY) FROM EMPLOYEES WHERE SALARY > 55000;

In the above statement, we are retrieving the employee salary from the employee's table whose salary is greater than 55000, where we are eliminating the duplicate values. Distinct salary will display unique salary from the employee's table.

The output for the following statement:

SALARY

65000

60000

55500

65500

58500

60500

In the first example, we have fetched the employee details whose salary is greater than 55000. The query result includes duplicate values. But in the second example, we execute the same query to display employee salary whose salary is greater than 55000. The query displays the result eliminating the duplicate values because we used the DISTINCT keyword followed by the salary column.

Example 3: Write a query to display employee city and salary from employees table without using a distinct keyword.

SELECT SALARY, CITY FROM EMPLOYEES;

We have displayed the employee salary and city name from the employee's table in the above statement, including duplicate values.

The output for the following statement:

SALARY	CITY
65500	PUNE
60000	NOIDA
50500	JAIPUR
55500	CHANDIGARH
65500	PUNE
50000	MUMBAI
55500	PUNE
50500	JAIPUR
58500	MUMBAI
60500	MUMBAI
54500	NOIDA
60000	DELHI
55500	DELHI

Example 4: Write a query to display employee city and salary from employees table using distinct keywords.

SELECT DISTINCT SALARY, CITY FROM EMPLOYEES;

We have fetched the employee salary and city from the employee table in the above statement. But we have fetched a unique value that is not repeated in the entire table using the DISTINCT keyword.

The output for the following statement:

SALARY	CITY
65500	PUNE
60000	NOIDA
50500	JAIPUR
55500	CHANDIGARH
50000	MUMBAI
55500	PUNE
58500	MUMBAI
60500	MUMBAI
54500	NOIDA
60000	DELHI
55500	DELHI

Example 5: Write a query to display an employee last name and department from the employee's table where the department name start with 'O' without using the DISTINCT keyword;

SELECT LAST_NAME, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT LIKE 'O%';

In the above statement, we fetched employee last names, departments from the employee's table of those employees whose department names start with 'O', including duplicate values.

The output for the following statement:

LAST_NAME	DEPARTMENT
MISHRA	ORACLE
SHARMA	ORACLE
SHARMA	ORACLE
AGARWAL	ORACLE

Only four records are displayed of those employees whose department starts with 'O'.

Example 6: Write a query to display an employee last name and department from the employee's table where the department name start with 'O' using the DISTINCT keyword;

SELECT DISTINCT LAST_NAME, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT LIKE 'O%';

We have used the same query in example 5, just added the DISTINCT keyword just before columns name that will display unique values from the employee's table whose department name starts with 'O'.

The output for the following statement:

LAST_NAME	DEPARTMENT
MISHRA	ORACLE
SHARMA	ORACLE
AGARWAL	ORACLE

The same query we have executed in the above query, we added the DISTINCT keyword just before columns. The output we get differs from the above example because the result display has unique values.

Example 7: Write a query to count employee salary from employees table without using a distinct keyword.

SELECT COUNT (SALARY) AS TOTAL SALARY FROM EMPLOYEES;

In the above statement, we have counted the total number of salaries from the employee's table, including duplicate values.

The output for the following statement:

TOTAL SALARY

13

Example 8: Write a query to count employee salary from employees table using distinct keyword.

SELECT COUNT(DISTINCT SALARY) AS TOTAL SALARY FROM EMPLOYEES;

In the above statement, we have counted the total number of salaries from the employee's table, excluding duplicate values.

The output for the following statement:

TOTAL SALARY

8