How to list multiple field matches from a single table in oracle SQL

Question

I have the following table:

create table Likes(ID1 number(5), ID2 number(5));
insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

The table contains the 'likes' of users identified by their IDs. Liking is a one way connection (If ID1 likes ID2 does not mean that TD2 is liking ID1).

I want to find those IDs, where is a two way connection (where the 'liker' is 'liked back' by user he likes).

I am beginner with Oracle SQL, I hope my question isn't that banal...

Answer 1

You can do it with a join:

SELECT t.id1,t.id2
FROM Likes t
INNER JOIN Likes s
 ON(t.id1 = s.id2 and t.id2 = s.id1)

Or with EXISTS()

SELECT t.*
FROM Likes t
WHERE EXISTS(select 1 FROM Likes s
             WHERE t.id1 = s.id2
                 AND t.id2 = s.id1)

Answer 2

try this:

with tab as (select id1, id2 from Likes)
select id1, id2 from tab
intersect
select id2, id1 from tab;

It should perform better, becuase it'll read Likes table only once