MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (`user_id` INTEGER, `name` VARCHAR(6));

INSERT INTO users (`user_id`, `name`) VALUES
  ('1', 'Me'),
  ('2', 'Jeremy'),
  ('3', 'Bob'),
  ('4', 'Kelvin');

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE friends (`user_id` INTEGER, `friend_id` INTEGER);

INSERT INTO friends (`user_id`, `friend_id`) VALUES
  ('1', '2'), ('1', '3'), ('1', '4'), 
  ('2', '1'), ('2', '3'), 
  ('3', '1'), ('3', '2'), 
  ('4', '1');

Records: 8  Duplicates: 0  Warnings: 0


SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = 1
GROUP BY u.user_id, u.name;


user_id
name
mutual_friends


2
Jeremy
1

3
Bob
1

4
Kelvin
0


SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = 2
GROUP BY u.user_id, u.name;


user_id
name
mutual_friends


1
Me
1

3
Bob
1


SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = 3
GROUP BY u.user_id, u.name;


user_id
name
mutual_friends


1
Me
1

2
Jeremy
1


SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = 4
GROUP BY u.user_id, u.name;


user_id
name
mutual_friends


1
Me
0