How to get all users that the logged in user is not friends with in MySQL?

Question

I am fully able to get all of the logged in users current friends, which I do with this query here -

SELECT users.*
FROM users
LEFT JOIN friends
ON users.id = friends.friend_id
WHERE friends.user_id = $user_logged_in_id

But I want to do the opposite, and I can't figure it out.

I am trying to do friends logic.

I need to get all the users who the currently logged in user is not already friends with

I have a users table and a friends table to act as a many to many relationship between users and their friends. The keys in the friends table are user_id friend_id.

To illustrate that someone is friends with another user I put (say the logged in user id is 3, and friends user id is 6) user_id = 3 and friend_id = 6 as one row, and then put another row with the id's flipped to illustrate the friendship the other way, user_id = 6 and friend_id = 3.

users table

|---------------------|------------------|
|          id         |     username     |
|---------------------|------------------|
|          1          |     sonja12      |
|---------------------|------------------|
|          2          |     dorris32     |
|---------------------|------------------|
|          3          |     billy32      |
|---------------------|------------------|
|          4          |     micha97      |
|---------------------|------------------|
|          5          |     travis841    |
|---------------------|------------------|
|          6          |     johnny28     |
|---------------------|------------------|

friends table

|---------------------|------------------|------------------|
|          id         |     user_id      |     friend_id    |
|---------------------|------------------|------------------|
|          1          |        3         |         6        |
|---------------------|------------------|------------------|
|          2          |        6         |         3        |
|---------------------|------------------|------------------|

EDIT:

I think I have narrowed down my question a bit to be more concise.

I want to get all users from the users table where their id does not appear as a friend id beside the user_id of the current logged in user in the friends table.

Answer 1

You're thinking of the problem in a strange way. It might help to think of it as an intersection of sets you need to create as opposed to a set that exists in the absence of an id.

SELECT id from users WHERE id != $logged_in_user AND id NOT IN ( SELECT friend_id from friends where user_id=$logged_in_user)

Answer 2

For bigger Datasets I would use a NOT EXISTS subquery query:

select u.*
from users u
where u.id <> $user_logged_in_id
  and not exists (
    select *
    from friends f
    where f.user_id = $user_logged_in_id
      and f.friend_id = u.id
)

or a LEFT (ANTI) JOIN:

select u.*
from users u
left join friends f
  on  f.user_id = $user_logged_in_id
  and f.friend_id = u.id
where u.id <> $user_logged_in_id
  and f.friend_id is null

I think the execution plan for both queries should be almost the same.

Demos: http://rextester.com/KUOZ9198