Wondering how Facebook does the “Mutual friends” feature

Say you have a table that is named Users and the Primary Key is UserID. Then you have a table called Friends with 2 columns called UserID (PK) and FriendUserID.

Say you have 2 users, 20 and 50.

When 20 adds 50 as friend, the application adds a new row:

INSERT INTO `Friends` (`UserID`, `FriendUserID`) VALUES (20, 50)

and when 50 confirms friendship, you add another row with values switched:

INSERT INTO `Friends` (`UserID`, `FriendUserID`) VALUES (50, 20)

When you want to find mutual friends between 20 and 50, simply:

SELECT `UserID` FROM `Friends` AS `A`, `Friends` AS B WHERE `A`.`FriendUserID` = 20 AND `A`.`UserID` = `B`.`UserID` AND `B`.`FriendUserID` = 50