JIYIK CN >

This tutorial discusses how to check for duplicates in a MySQL table and retrieve the number of duplicates using the SELECT DISTINCTand statements, respectively.SELECT COUNT

SELECT DISTINCTCheck for duplicates in a MySQL table using

The impact of having duplicate records in a database table can range from a minor inconvenience to a disaster. Fortunately, MySQL has some nifty keywords that can be combined to scan a table for duplicates.

Additionally, we can count the occurrences of duplicate records and remove them if necessary.

Let's employee_detailscreate a test_companydatabase with a sample table named .

CREATE DATABASE test_company;
USE test_company;

CREATE TABLE employees_details(
    id INT AUTO_INCREMENT,
    name VARCHAR (255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    salary INT,

    PRIMARY KEY (id)
);

The table is populated with values, including duplicate values.

INSERT INTO employees_details (name, title, salary) Values
('James Maddison','Computer Engineer',80000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000),
('Carlos Rodriguez','Data Engineer',100000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000);

SELECT * FROM employees_details;

Output:

+----+------------------+--------------------------+--------+
| id | name             | title                    | salary |
+----+------------------+--------------------------+--------+
|  1 | James Maddison   | Computer Engineer        |  80000 |
|  2 | Matthew Defoe    | Software Architect       | 150000 |
|  3 | Daniel Jameson   | Software Engineer II     |  95000 |
|  4 | Jules Reddington | Senior Software Engineer | 120000 |
|  5 | Carlos Rodriguez | Data Engineer            | 100000 |
|  6 | Matthew Defoe    | Software Architect       | 150000 |
|  7 | Daniel Jameson   | Software Engineer II     |  95000 |
|  8 | Jules Reddington | Senior Software Engineer | 120000 |
+----+------------------+--------------------------+--------+
8 rows in set (0.00 sec)

There are three duplicates in this table, which are easy to spot due to the small size of the table. We use SELECT DISTINCTthe statement for the larger table to retrieve unique records from the table.

According to the official documentation, SELECT DISTINCTthe statement retrieves only one instance of the output record, even if it occurs multiple times.

Therefore, when SELECT DISTINCTthe number of records returned by the statement is less than the total number of records in the table, we can determine that there are duplications.

-- Retrieving only distinct records.
SELECT DISTINCT name,title,salary FROM employees_details;

Output:

+------------------+--------------------------+--------+
| name             | title                    | salary |
+------------------+--------------------------+--------+
| James Maddison   | Computer Engineer        |  80000 |
| Matthew Defoe    | Software Architect       | 150000 |
| Daniel Jameson   | Software Engineer II     |  95000 |
| Jules Reddington | Senior Software Engineer | 120000 |
| Carlos Rodriguez | Data Engineer            | 100000 |
+------------------+--------------------------+--------+
5 rows in set (0.00 sec)

Note that idthe column is not included in the query because MySQL ididentifies the column as a unique record.

Therefore, including the column in a query idreturns all records (including duplicates) as unique records.

From the result set, we can infer that there are three (3) duplicate records because the query returns five (5) different records and there are eight (8) records in the table.

Use SELECT COUNTthe statement to find the number of repetitions in a MySQL table

Now, after detecting the presence of duplicates, we can use SELECT COUNTthe statement to find the number of occurrences of the duplicate.

SELECT name AS 'employee name', COUNT(*) AS Occurrence FROM employees_details
GROUP BY name
HAVING Occurrence > 1;

Output:

+------------------+------------+
| employee name    | Occurrence |
+------------------+------------+
| Daniel Jameson   |          2 |
| Jules Reddington |          2 |
| Matthew Defoe    |          2 |
+------------------+------------+
3 rows in set (0.001 sec)

This will retrieve the duplicate records and the number of duplicates for each record in the table. As expected, there are three (3) duplicate records.

SELECT COUNTThe use of statements is discussed further through this official reference.

Use INNER JOINthe statement to view duplicate records in the MySQL table

We can SELECT DISTINCTuse INNER JOINa query between the target table and a query to see the duplicate records next to the primary record.

SELECT I.id, O.name, O.title, O.salary
FROM employees_details AS I
INNER JOIN(SELECT DISTINCT name, title, salary FROM employees_details) AS O
ON I.name = O.name;

Output:

+----+------------------+--------------------------+--------+
| id | name             | title                    | salary |
+----+------------------+--------------------------+--------+
|  1 | James Maddison   | Computer Engineer        |  80000 |
|  2 | Matthew Defoe    | Software Architect       | 150000 |
|  6 | Matthew Defoe    | Software Architect       | 150000 |
|  3 | Daniel Jameson   | Software Engineer II     |  95000 |
|  7 | Daniel Jameson   | Software Engineer II     |  95000 |
|  4 | Jules Reddington | Senior Software Engineer | 120000 |
|  8 | Jules Reddington | Senior Software Engineer | 120000 |
|  5 | Carlos Rodriguez | Data Engineer            | 100000 |
+----+------------------+--------------------------+--------+
8 rows in set (0.001 sec)