Show all rows with duplicate from single column

Question

For example, I have the following table :

id     user_id     name     age     address
1       12         John      21      earth
2       13         Daniel    19      planet
3       12         Paul      25      here
4       11         Joana     23      mars
5       11         Paul      18      earth

The results that I want :

id     user_id     name     age     address
1       12         John      21      earth
3       12         Paul      25      here
4       11         Joana     23      mars
5       11         Paul      18      earth

So basically, I want to show all rows from duplicated values in the user_id column. I am new to SQL and hopefully, you guys can help me. Thanks in advance.

Does this answer your question? [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — FanoFN, Sep 07 '21 at 03:19

score 2 · Accepted Answer · answered Sep 07 '21 at 03:22

2

You can do something like below.

select * from your_table where user_id in (
    select user_id from your_table 
    group by user_id having count(*) > 1
)

answered Sep 07 '21 at 03:22

Bishan

15,211
52
164
258

This is exactly what I am looking for! Thanks a lot. – Hiraeths Sep 07 '21 at 03:32

score 0 · Answer 2 · answered Sep 07 '21 at 11:56

I would recommend exists for this purpose:

select t.*
from t
where exists (select 1 from t t2 where t2.user_id = t.user_id and t2.id <> t.id)
order by user_id, id;

In general, it is best to avoid aggregation functions in subqueries if you can -- for performance reasons.

Show all rows with duplicate from single column

2 Answers2