Improve query: find users who are neither friends nor fans of a user

Question

Please take a look at this query and try to give me any other ideas that will give the exact same results more efficiently.

SELECT username
FROM users
WHERE username NOT IN (
    SELECT DISTINCT username
    FROM users, friends 
    WHERE 'user1' IN (you,friend,username) 
      AND you     IN ('user1',username) 
      AND friend  IN ('user1',username))
  AND username <> 'user1'

Here's a live demo.

Answer 1

Assuming I've got it right and the you and friend columns come from the friends table and username from users, here's another possibility (which could be viewed as a development on @Malachi's suggestion):

SELECT username FROM users
WHERE username NOT IN (
  SELECT CASE you WHEN 'user1' THEN friend ELSE you END
  FROM friends
  WHERE you = 'user1' OR friend = 'user1'
)
AND username <> 'user1'
;

Basically, same approach as @Malachi's, except one fewer scan of the friends table, although it may turn out that their solution can use indices more efficiently.

Answer 2

This query will give you the same results

SELECT username FROM users 
WHERE username NOT IN (SELECT you FROM friends WHERE friend = 'user1') 
AND username NOT IN (SELECT friend FROM friends WHERE you = 'user1')
AND username <> 'user1'

and the SQLFiddle

If you look at the execution plan, it looks like my query grabbed 1 less row throughout the execution and it didn't have to use a temporary or a join buffer.

This Query is more readable than yours, assuming this is what you are trying to do.