SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
SQL SELECT DISTINCT

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

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:

SALARYCITY
65500PUNE
60000NOIDA
50500JAIPUR
55500CHANDIGARH
65500PUNE
50000MUMBAI
55500PUNE
50500JAIPUR
58500MUMBAI
60500MUMBAI
54500NOIDA
60000DELHI
55500DELHI
SQL SELECT DISTINCT

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:

SALARYCITY
65500PUNE
60000NOIDA
50500JAIPUR
55500CHANDIGARH
50000MUMBAI
55500PUNE
58500MUMBAI
60500MUMBAI
54500NOIDA
60000DELHI
55500DELHI
SQL SELECT DISTINCT

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_NAMEDEPARTMENT
MISHRAORACLE
SHARMAORACLE
SHARMAORACLE
AGARWALORACLE
SQL SELECT DISTINCT

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_NAMEDEPARTMENT
MISHRAORACLE
SHARMAORACLE
AGARWALORACLE
SQL SELECT DISTINCT

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

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



ADVERTISEMENT
ADVERTISEMENT