MySQL query that selects non-friends of a user

I have the following schema of a Mysql database:

User(Id, FirstName, LastName, NickName, Etc.)

Request(SenderId, ReceiverId)

Friendship(Id1, Id2)

I consider friendship to be an undirected relation, which means that for every friendship, I insert it twice to the Friendship table. (Let me know if this is not a good idea, please).

What I am trying to retrieve is a list of users, who are not friends to a specific user (let me name him UserX), nor have a current request ongoing to/from him.

My initial trials led me to this:

SELECT User.Id, User.NickName, User.Picture FROM User 
LEFT JOIN Friendship A ON User.Id = A.Id1
LEFT JOIN Friendship B ON User.Id = B.Id2 
LEFT JOIN Request C ON User.Id = C.Sender
LEFT JOIN Request D ON User.Id = D.Reciever 
WHERE User.Id <> ? 

And, of course the placeholder is UserX's Id.

This doesn't work because, although the tuples that has friendships or requests with UserX are eliminated, The friends still appear because they have friendships with other users!

Thanks in advance.

If you want an efficient solution, use not exists multiple times:

select u.*
from user u
where not exists (select 1 from friendship f where f.id1 = u.id and f.id2 = ?) and
      not exists (select 1 from friendship f where f.id2 = u.id and f.id1 = ?) and
      not exists (select 1 from request r where r.SenderId = u.id and r.ReceiverId = ?) and
      not exists (select 1 from request r where r.ReceiverId = u.id and r.SenderId = ?);

In particular, this can take advantage of indexes on:

• friendship(id1, id2)
• friendship(id2, id1)
• request(SenderId, ReceiverId)
• request(ReceiverId, SenderId)

This should have much better performance than solutions that union subqueries together.