How to Find Duplicate Records in SQL – With & Without DISTINCT Keyword

In our last discussion, we saw the SQL Statements. Today, we will see Duplicate Records in SQL. Here, we will discuss a query to find duplicate rows in a database. Also, we will see how can we delete duplicate records in SQL with and without the DISTINCT keyword.

So, let us start Duplicate Records in SQL.

Duplicate Records in SQL

How to Find Duplicate Records in SQL – With & Without DISTINCT Keyword

1. What are Duplicate Records in SQL?

When we need to fetch information from the table, there must be duplicate multiple data or records available in the table. So to fetch only unique records and avoid fetching duplicate records SQL uses certain ways.

First is using the DISTINCT keyword and second without using the DISTINCT keyword.

2. Using DISTINCT Keyword to Delete the Duplicate Records

The DISTINCT keyword can be used with the select statement to eliminate all the duplicate records and only fetching the unique records.

Syntax – 

The basic syntax of a DISTINCT keyword to eliminate duplicate record is

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Recommended Reading – SQL Query Interview Questions

For example,

Let us create a table named- Worker

WORKER_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
001NIHARIKA ARORA200002013-02-25 09:00:00HR
002AYUSHIGURONDIA50002015-02-10 09:00:00ADMIN
003PRIYANSHA CHOUKSEY250002014-05-16 09:00:00HR
004APARNA DESHPANDE80002016-12-20 09:00:00ADMIN
005SHAFALIJAIN200002015-08-29 09:00:00ADMIN
006SUCHITA JOSHI200002017-02-12 09:00:00ACCOUNT
007SHUBHIMISHRA250002018-03-23 09:00:00ADMIN
008DEVYANI PATIDAR180002014-05-02 09:00:00ACCOUNT

We will first use only SELECT query returns duplicate salary records.

SQL> SELECT SALARY FROM CUSTOMERS
  ORDER BY SALARY;

Let’s discuss Dynamic SQL

This would produce the following result, where the salary of 20000 and 25000 comes twice thus creating a duplicate record.

5000

8000

18000

20000

20000

20000

25000

25000

Now, we will use the DISTINCT with the SELECT query.

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
  ORDER BY SALARY;

Result

5000

8000

18000

20000

25000

3. How to Delete SQL Duplicate Records Without DISTINCT Keyword?

You must read – SQL Stored Procedures

i. Delete Duplicate Records in SQL Using Row_Number.

WITH CTE (Col1, Col2, Col3, DuplicateCount)
AS
(
 SELECT Col1, Col2, Col3,
 ROW_NUMBER() OVER(PARTITION BY Col1, Col2,
      Col3 ORDER BY Col1) AS DuplicateCount
 FROM MyTable
) SELECT * from CTE Where DuplicateCount = 1

ii. Delete Duplicates Records in SQL Using Self Join

YourTable

emp_name      emp_address sex    matial_status
uuuu                  eee m      s
iiii                       iii f        s
uuuu                 eee  m s
SELECT emp_name, emp_address, sex, marital_status
from YourTable a
WHERE NOT EXISTS (select 1
        from YourTable b
        where b.emp_name = a.emp_name and
              b.emp_address = a.emp_address and
              b.sex = a.sex and
              b.create_date >= a.create_date)

iii. Delete Duplicate Records in SQL Using group By

SELECT FirstName, LastName, MobileNo, COUNT(*) as CNT
FROM  CUSTOMER
GROUP BY FirstName, LastName, MobileNo;
HAVING COUNT(*)  = 1

4. Summary

Hence, in this tutorial, we studied SQL Duplicates. We discussed how to find SQL duplicates rows and how to delete duplicate rows in SQL. Moreover, we discussed a query to find duplicate rows in a table. Also, we saw the use of DISTINCT Keyword in SQL. 

Still, if you have doubt, ask in the comment tab.

Also check –

Most asked SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.