Summary -

In this topic, we described about the Handling Duplicates with detailed example.

HANDLING DUPLICATES is that how we can handle duplicate values in existing table. If the entire row has duplicate values, we can delete that rows. If specified columns have the duplicate values, we can handle with them in different ways like by using DISTINCT keyword.

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

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Getting identical values from two tables.

Requirement– Getting identical values from employee_details, department_details tables. The query was as follows – 

SELECT DISTINCT e.manager_id 
FROM employee_details e, department_details e1
WHERE e.dept_id = e1.dept_id;

By exeuting above query, we can get DISTINCT values like as shown in below -

manager_id
Employee1
Employee2
Employee3
Employee4

Scenario – Getting duplicate values from table.

Requirement – Getting duplicate values from employee_details table. The query was as follows – 

SELECT manager_id, count(*) FROM employee_details 
GROUP BY manager_id
HAVING COUNT(*) > 1;

By executing above query, we can duplicate values from employee_details like as shown in below -

manager_id No_column_name
2
Employee3 2
Employee4 2