Unique pair checking query improving in pivot table First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill.

I have seen an example online.

So I have a pivot table which contains two column: profile_id, followed_id, each column a reference to the Profile.id column, this is a friend list.

I created a query to check for self-loops like:

profile_id | followed_id
         3 | 5
         5 | 3


As in the link this is a "duplication" because it represents the same relation between the profiles.

I created a query to check for duplication, this is a challenge for me. Yes, I added unique constraints to the table, but I was curious about I can write a checker query or not.

This is what I figured out:

SELECT *
  FROM
    tbl_profile_follow A,
    tbl_profile_follow B
  WHERE
    (B.profile_id = A.profile_id AND B.followed_id = A.followed_id)
    AND
    (B.profile_id = A.followed_id AND B.followed_id = A.profile_id)


I have thinking about there are any more effective query for this problem or not. For me it seems I cannot transform this query to join because my pivot table does not use primary key.
However if I use primary key in this pivot table and try to user join will I get better performance? 

Of course I apply postgres specific query to learn postgresql :)

Thanks for the answers
The query can be made easier:

select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*)
from tbl_profile_follow 
group by least(profile_id,followed_id), greatest(profile_id, followed_id)
having count(*) &gt; 1;


Using this approach you can also create a unique index on the table

create unique index idx_unique_pair 
   on tbl_profile_follow (least(profile_id,followed_id), greatest(profile_id, followed_id));


This above will however not prevent or detect multi-level loops:

profile_id | followed_id
         3 |           5
         5 |           6
         6 |           3