Select where combined with exists

Question

Table dating_users:

 user_id | username
---------+----------
      11 | taimoor
      13 | jacob
      14 | pi
(3 rows)

Table dating_likes:

 like_id | user_id | liked_user
---------+---------+------------
      54 |      11 |         13
      56 |      13 |         11
      57 |      11 |         14
(3 rows)

I want to find likes where a user has liked someone, and that someone has liked them. Here's my query:

select like_id from dating_likes where user_id = 11 and exists(select like_id from dating_likes where liked_user = 11);

Result:

 like_id
---------
      54
      57
(2 rows)

That should have resulted with only the first row, since 54 is a like where the user_id = 11 and there is a like 56 where liked_user = 11. Like 57 should not show up, since it's not mutual - all that's true is that the user_id is 11.

Answer 1

Think about it as two different likes that have to equal:

select b_likes_a.liked_user
from dating_likes a_likes_b
join dating_likes b_likes_a
  on  a_likes_b.user_id = b_likes_a.liked_user
  and a_likes_b.liked_user = b_likes_a.user_id
where a_likes_b.user_id = 11

a_likes_b and b_likes_a are table aliases of dating_likes.

From this we also see that like_id is meaningless and could be omitted. (user_id,liked_user) is a sufficient primary key.

Answer 2

This is my solution

select t.*
from dating_likes t
where exists (select t1.like_id,t1.user_id
              from dating_likes t1
              where t.user_id = t1.liked_user and
                    t.liked_user = t1.user_id)

The output is this :

like_id  User_id   Liked_user
 56       13          11
 54       11          13

I don't know which solution(using Join or Exist) is better in terms of performance but I like to know if anyone wants to share their opinion. Thanks