How to convert a correlated nested subquery to an uncorrelated one?

Question

MySQL

There is a table with athletes, in it the athlete id fields are unique not null and the sport, you need to display those who are involved in more than one sport. how to do this without a correlated subquery? (in other words, display the duplicate rows of the table as many times as they are repeated)
Is it possible to somehow more efficiently execute this query?
My code

select  pair_sports.fio ,pair_sports.sport
 from
 fitness.pair_sports
 where fio=any (SELECT pair_sports.fio
 FROM   fitness.pair_sports
 group by (fio)
 having count(*)>1)
 order by fio

Answer 1

In order to make an analysis, create a schema with the required DBMS on the sqlfiddle.com website and link to your schema, then we'll see. According to the preliminary analysis, I see that the question is in the MYSQL categories, since I did not work with this DBMS, I will try to write a selection in simple SQL and something similar to MYSQL.
1) Simple and readable option:

select  t.*
 from fitness.pair_sports t
 where exists 
 (
 select t2.fio 
 from fitness.pair_sports t2
   where t2.fio = t.fio
 group by t2.fio
   having count(*) > 1
 );

2) Some kind of muddy option, it might work

select * from
(
select  @rownum:= case when t.fio <> @name then 0 else 1 end as rn,
        @name:=t.fio as name,
        t.*
 fitness.pair_sports t
order by t.fio
) tt
where tt.rn = 1;