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
  • 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\$ Oct 22, 2013 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, 2013 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, 2013 at 6:58

2 Answers 2

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.

\$\endgroup\$
1
  • \$\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, 2013 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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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