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 ?
15 Jan 2021 at 06:47 PM
0pnshow more
StorStor
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.
16 Jan 2021 at 03:14 PM
0pnshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook & Twitter
2022 AnswerTabsTermsContact us