By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Users (`user_id` INTEGER, `username` VARCHAR(6), `password` VARCHAR(7));
INSERT INTO Users (`user_id`, `username`, `password`) VALUES
('1', 'elexis', '*******'),
('2', 'rooney', '*******'),
('3', 'wayne', '*******'),
('4', 'June', '*******');
CREATE TABLE Friends (`user_id` INTEGER, `friend_id` INTEGER);
INSERT INTO Friends (`user_id`, `friend_id`) VALUES
('1', '3'),
('2', '1'),
('2', '4'),
('1', '2');
Records: 4 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
SELECT f.*, u.username
FROM Users u
INNER JOIN (
SELECT user_id, friend_id FROM Friends WHERE user_id = 1
UNION
SELECT friend_id, user_id FROM Friends WHERE friend_id = 1
) f ON f.friend_id = u.user_id
| user_id | friend_id | username |
|---|---|---|
| 1 | 2 | rooney |
| 1 | 3 | wayne |
SELECT f.*, u.username
FROM Users u
INNER JOIN (
SELECT user_id, friend_id FROM Friends
UNION
SELECT friend_id, user_id FROM Friends
) f ON f.friend_id = u.user_id
WHERE f.user_id = 1
| user_id | friend_id | username |
|---|---|---|
| 1 | 2 | rooney |
| 1 | 3 | wayne |