CODEWITHSUNDEEP
mysqlsqlselectjoinquery-performance
Marcus
Marcus

Reputation: 295

How to use JOIN instead of sub query (NOT IN)

I want to list all people that $_SESSION['userid'] follows and not follows

This query below is for follows cast, it works

IN (slow)

SELECT user.* FROM user 
WHERE user.userid 
     IN (SELECT follow.followtoid FROM follow 
WHERE follow.followerid = $_SESSION['userid'])

JOIN (fast)

SELECT p.* 
FROM user p
    JOIN follow f ON p.userid = f.followtoid
WHERE f.followerid = $_SESSION['userid']

For those still not follow cast, it works but seems very slow
Please suggest me how do i use JOIN instead of NOT IN

NOT IN (slow)

SELECT user.* FROM user 
WHERE user.userid 
     NOT IN (SELECT follow.followtoid FROM follow 
WHERE follow.followerid = $_SESSION['userid'])

Upvotes: 3

Views: 5516

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT u.* 
FROM user u
LEFT JOIN follow f ON u.userid = f.followtoid AND f.followerid = $_SESSION['userid']
WHERE f.followtoid IS NULL;

OR

SELECT u.* 
FROM user u
WHERE NOT EXISTS (SELECT 1 FROM follow f 
                  WHERE u.userid = f.followtoid AND f.followerid = $_SESSION['userid']
                 );

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

You could use LEFT JOIN and eliminate all rows that have matches;

SELECT p.* 
FROM user p
LEFT JOIN follow f 
  ON p.userid = f.followtoid
 AND f.followerid = $_SESSION['userid']
WHERE f.followtoid IS NULL

Upvotes: 4

Related Questions