O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Identifying Duplicates using SELF JOIN

You want to find the duplicate rows in a table for specific columns. For example, you want to find out which employees have the same first_name, same last_name, same gender, and same hire_date. In that case, you can join the employees table with itself while specifying the columns where you want to find duplicates in the JOIN clause. You need to use different aliases for each table.

You need to add an index on the columns you want to join. The indexes will be discussed in Chapter 13, Performance Tuning. For now, you can execute this command to add an index:

mysql> ALTER TABLE employees ADD INDEX name(first_name, last_name);Query OK, 0 rows affected (1.95 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required