Retrieve duplicate column values in MySQL

How to find duplicate column values in MySQL

Let's see some of the many ways to retrieve duplicate column value from MySQL table. In this example, we are going to find and return duplicate contact names from the contacts table. It is up to you to decide which is applicable according to the scenario.

Approach 1:

SELECT customer_name FROM contacts group by customer_name having count(*) > 1;

OR

SELECT 

    customer_name, 

    COUNT(customer_name)

FROM

    contacts

GROUP BY customer_name

HAVING COUNT(customer_name) > 1;

Result:

Approach 2:

SELECT contact_id, 

   contact_type, 

   contacts.customer_name 

FROM contacts

   INNER JOIN (SELECT customer_name

               FROM   contacts

               GROUP  BY customer_name

               HAVING COUNT(contact_id) > 1) dup

           ON contacts.customer_name = dup.customer_name;

Result: