mysql - 是否可以将此查询转换为连接？

Question

这是查询:

SELECT *
FROM square_achievements_achievements
JOIN square_achievements_achievement_counters ON square_achievements_achievement_counters.SAA_ID = square_achievements_achievements.SAA_ID
JOIN square_achievements_counters ON square_achievements_counters.SAC_ID = square_achievements_achievement_counters.SAC_ID
WHERE square_achievements_counters.eventObject = 'CommentEvent'
AND square_achievements_counters.eventType = 'add'
AND square_achievements_achievements.SAA_ID NOT IN
(
    SELECT square_achievements_achievements.SAA_ID 
    FROM square_achievements_achievements 
    JOIN square_achievements_user_achievements ON square_achievements_user_achievements.SAA_ID = square_achievements_achievements.SAA_ID
    WHERE square_achievements_user_achievements.UID = 83
)

如果可能的话，将这个查询写成一个连接会不会更有效率？如果是这样，它将如何重写？

Answer 1

SELECT  a.*
FROM    achievements a
JOIN    achievement_counters ac
ON      ac.saac_id = a.saa_id
JOIN    counters c
ON      c.sac_id = ac.sac_id
WHERE   c.eventObject = 'CommentEvent'
        AND c.eventType = 'add'
        AND a.saa_id NOT IN
        (
        SELECT  saa_id
        FROM    user_achievements 
        WHERE   uid = 83
        )

这还不错。

如果你想加入，使用这个:

SELECT  a.*
FROM    achievements a
JOIN    achievement_counters ac
ON      ac.saac_id = a.saa_id
JOIN    counters c
ON      c.sac_id = ac.sac_id
LEFT JOIN
        user_achievements ua
ON      ua.uid = 83
        AND ua.saa_id = a.saa_id
WHERE   c.eventObject = 'CommentEvent'
        AND c.eventType = 'add'
        AND ua.saa_id IS NULL