mysql join 替代办法_mysql-如何使用JOIN代替子查询(NOT IN)

我想列出$_SESSION [‘userid’]跟随但不跟随的所有人员

以下查询适用于以下演员,它可以正常工作

IN(慢)

SELECT user.* FROM user

WHERE user.userid

IN (SELECT follow.followtoid FROM follow

WHERE follow.followerid = $_SESSION['userid'])

加入(快速)

SELECT p.*

FROM user p

JOIN follow f ON p.userid = f.followtoid

WHERE f.followerid = $_SESSION['userid']

对于那些仍不遵循演员表的人,它可以工作,但是看起来很慢

请建议我如何使用JOIN而不是NOT IN

不在(慢)

SELECT user.* FROM user

WHERE user.userid

NOT IN (SELECT follow.followtoid FROM follow

WHERE follow.followerid = $_SESSION['userid'])

解决方法:

您可以使用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

标签：query-performance,join,select,sql,mysql

来源： https://codeday.me/bug/20191029/1959873.html