5
\$\begingroup\$

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.

\$\endgroup\$
  • 3
    \$\begingroup\$ As with all SQL query optimizations, first run EXPLAIN SELECT username FROM users WHERE… and check that the appropriate indexes are in place. \$\endgroup\$ – 200_success Oct 22 '13 at 23:29
  • \$\begingroup\$ 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. \$\endgroup\$ – Malachi Oct 23 '13 at 13:37
  • \$\begingroup\$ 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. \$\endgroup\$ – Andriy M Oct 25 '13 at 6:58
4
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ the Execution plan that was given by SQLFiddle definitely looks more efficient than my answer! Nice use of the Case Statement \$\endgroup\$ – Malachi Oct 25 '13 at 13:31
7
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.