如何使用JOIN代替子查询(NOT IN)

Question

我希望列出所有$_SESSION['userid']关注和未关注的人。

以下查询用于关注列表，它可以正常工作。

IN（较慢）

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

JOIN（快速）

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

对于那些仍然不了解cast的人，它可以工作，但似乎非常慢。
请告诉我如何使用JOIN代替NOT IN。

NOT IN（慢）

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

- Marcus

Answer 1

您可以使用LEFT JOIN来消除所有有匹配的行；

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

- Joachim Isaksson

Answer 2

尝试这个：

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

- Saharsh Shah