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

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
FROM users, friends


Here's a live demo.

• As with all SQL query optimizations, first run EXPLAIN SELECT username FROM users WHERE… and check that the appropriate indexes are in place. – 200_success Oct 22 '13 at 23:29
• are you missing entries in the Friends table? should you have more entries that have you = 'user1' and friend = 'user3' and another for friend ='user4'...I am talking about the fiddle now. maybe it is just a subset of the data in the table. – Malachi Oct 23 '13 at 13:37
• It would be somewhat easier to work with your query if you prefixed column names with table aliases so that it was clear which column came from which table. – Andriy M Oct 25 '13 at 6:58

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
SELECT CASE you WHEN 'user1' THEN friend ELSE you END
FROM friends
WHERE you = 'user1' OR friend = '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.

• the Execution plan that was given by SQLFiddle definitely looks more efficient than my answer! Nice use of the Case Statement – Malachi Oct 25 '13 at 13:31

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')