Find and show rows in table with duplicate values in columns - SQL

Question

Find and show rows in table with duplicate values in columns - SQL

Could you please provide an example of SQL, that will get table rows with duplicate values in the specific columns ?

0

Report

15 Jan 2021 at 06:47 PM

Stor

duplicates

sql

1 answer

OK

Tim · Answer 1

Hi,

You can get rows with duplicate values in the specific columns, by using SELECT in combination with GROUP, HAVING and COUNT. The following examples are focused on column email_address.

To see the occurrence of email addresses in the column, you can use this SQL:

SELECT email_address, COUNT(*) FROM users GROUP BY email_address HAVING COUNT(*) > 1;

To find and show the rows with duplicate values in the column, you can use this SQL with nested query:

SELECT * FROM users WHERE email_address IN (SELECT email_address FROM users GROUP BY email_address HAVING COUNT(*) > 1);
The nested queries may take longer time to execute.

Find and show rows in table with duplicate values in columns - SQL

duplicates

sql

Error: Illegal parameter data types INT and row for operation '='

Is faster one big SQL query or multiple small SQL queries ?

Fix error: mysqli_select_db / query expects 2 arguments, 1 given

Copy and update rows in the same table with autoincrement - SQL

Fix SQL error Index for table 'user_db' is corrupted, try to repair it

Insert or update current date in DATETIME format - SQL

MySQL error: Unknown table engine 'InnoDB' - how to fix ?