INNER JOIN table by itself

Question

I have a table that represents friendships between two users. Each entry is unidirectional; friendship requires two entries. I would like to save it that way.

user1_id | user2_id 
   43        44
   44        43

I'm curious what the best way to request such a setting. For example, how to request a list of all friends of a particular user?

The solution I came across is two inverse and internal, connecting a friendship table with itself, to first get a list of all complete pairs, and then use the usual WHERE clause:

SELECT f.user2_id 
    FROM friendships f
        INNER JOIN friendships f2
            ON f.user1_id = f2.user2_id 
            && f.user2_id = f2.user1_id
        WHERE f.user1_id = 43;

, , , . , , , , :

SELECT thread_id,owner_id,message,time  
    FROM threads th                             
        INNER JOIN friendships f                                
            ON th.owner_id = f.user2_id                             
            && f.user1_id = 43                           
        INNER JOIN friendships f2                               
            ON f.user2_id = f2.user1_id                             
            && f.user1_id = f2.user2_id

, . , , , . :

• , ?
• ?

Answer 1

, , (, ). , , . friendships .

. , .

SELECT f1.user1_id AS user, 
       f1.user2_id AS friend 
  FROM friendships AS f1
  JOIN friendships f2 
    ON  (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

. , , 43.

 SELECT friend
   FROM (
        SELECT f1.user1_id AS user, 
               f1.user2_id AS friend 
          FROM friendships AS f1
          JOIN friendships f2 
            ON  (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)
        ) AS friends 
  WHERE user = 43 

, , , .

 CREATE VIEW friends AS (
        SELECT f1.user1_id AS user, 
               f1.user2_id AS friend 
          FROM friendships AS f1
          JOIN friendships f2 
            ON  (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)
 )

, .

 SELECT friend FROM friends WHERE user = 43;

:

SELECT thread_id,owner_id,message,time  
  FROM threads AS th
  JOIN (
        SELECT f1.user1_id AS user, 
               f1.user2_id AS friend 
          FROM friendships AS f1
          JOIN friendships f2 
            ON  (f1.user1_id = f2.user2_id AND f1.user2_id = f2.user1_id)

       ) AS f ON th.owner_id = f.friend
 WHERE f.user = 43

, . , .

SELECT thread_id,owner_id,message,time  
  FROM threads AS th
  JOIN friends AS f ON th.owner_id = f.friend
 WHERE f.user = 43

(: JOIN INNER JOIN .)

, ? friends ( , ) . , , , WHERE f.user = 43.

friendships, , (user1_id, user2_id) (user2_id, user1_id)

( , , .)