Optimizing query to find mutual friends in Facebook

Question

Assuming a Facebook friend's table looks like this - userId | friendId, how would you write a SQL query to find the mutual friends between two friends? I have the below code that works for two friends A and B. However, I feel like it is not optimized enough and I was hoping I could do the same query with joins instead. Two areas where I am confused:

1. A particular user Id can be in either of the columns depending on who initiated the friend request. If A sent a request to B, userId would be A and friendId would be B. The below query I have handles this scenario but how would you do the same with a JOIN?
2. How does FB recommend adding someone as a friend? How would that look like in a query?

There are multiple similar questions on Stack Overflow/the internet but none seem to be 100% accurate!

I am looking for a MS SQL server query but any SQL language should work.

SELECT *
FROM (
SELECT CASE WHEN userId = 'A' THEN friendId ELSE userId END AS mutualFriends
FROM friendsTable 
WHERE userId = 'A' OR friendId = 'A'
UNION
SELECT CASE WHEN userId = 'B' THEN friendId ELSE userId END AS mutualFriends
FROM friendsTable 
WHERE userId = 'B' OR friendId = 'B'
) A
WHERE mututalFriends NOT IN ('A','B')

Answer 1

The query you provided is a UNION, it returns the union of friends of A and B.

The query below returns the JOIN of A's and B's friends - a list of people that are friends with A and B.

Fiddle: DB-Fiddle

SELECT a_mutualfriendid AS mutualfriendid FROM 

(SELECT distinct a_mutualfriendid
FROM (SELECT 
  case 
    when userid = 'A' then friendid 
    else userid 
  end as a_mutualfriendid
FROM friendsTable
WHERE userid in ('A') or friendid in ('A')
) a_friends
WHERE a_mutualfriendid NOT IN ('B')) a

INNER JOIN

(SELECT distinct b_mutualfriendid
FROM (SELECT 
  case 
    when userid = 'B' then friendid 
    else userid 
  end as b_mutualfriendid
FROM friendsTable
WHERE userid in ('B') or friendid in ('B')
) b_friends
WHERE b_mutualfriendid NOT IN ('A')) b

ON a_mutualfriendid = b_mutualfriendid

As far as how Facebook stores the data, that is proprietary information and employees probably won't be able to answer.

Here is a guess: Keeping in mind that if X is friends with Y, either of them could "ignore" the other, there are probably two records in the database, "X friends with Y" and "Y friends with X". The initial request from X creates the first record and the acceptance action from Y creates the second record. Each record could then store whether posts from the friend should show in other's feed.