mysql - 如何将此查询转换为连接查询

Question

请在不使用mysql中的子查询的情况下将以下查询转换为连接查询

SELECT u.id, u.name, u.avatar, u.slug, u.location
FROM user_registration as u
WHERE u.id <>'3' AND u.id NOT IN (SELECT f.user_id FROM followers as f WHERE f.follower_id ='3')
order by id

Answer 1

将查询移植到连接的一种方法是左连接 user_registration 和 followers 的子查询。保留记录的条件是用户注册 ID 不是 '3'，并且该记录与 followers 上的子查询的连接中的任何内容都不匹配>.

SELECT t1.id, t1.name, t1.avatar, t1.slug, t1.location
FROM user_registration t1
LEFT JOIN
(
    SELECT f.user_id
    FROM followers f
    WHERE f.follower_id = '3'
) t2
    ON t1.id = t2.user_id
WHERE t1.id <> '3' AND t2.user_id IS NULL
ORDER BY t1.id

我认为以下查询也应该有效:

SELECT t1.id, t1.name, t1.avatar, t1.slug, t1.location
FROM user_registration t1
LEFT JOIN followers t2
    ON t1.id = t2.user_id AND t2.follower_id = '3'
WHERE t1.id <> '3' AND t2.user_id IS NULL
ORDER BY t1.id