MySQL:从子查询到单个查询

Question

我有一个我认为可以优化的查询:

SELECT floors.id, floors.floor FROM floors
WHERE floors.societies_id = 1
AND floors.status = 'Y'
AND floors.id NOT IN (
    SELECT DISTINCT(floors.id) FROM floors
    INNER JOIN societies ON societies.id = floors.societies_id
    INNER JOIN resident_floors ON resident_floors.floors_id = floors.id
    WHERE societies.id = 1
    AND floors.status = 'Y'
)

这个查询可以使用吗？或者可以改进吗？

Answer 1

您似乎想要获取 resident_floors 中不存在的所有楼层。为此，我们可以保留加入 RF 并仅要求加入失败导致 RF 中为空的行:

SELECT floors.* FROM floors
INNER JOIN societies ON societies.id = floors.societies_id
LEFT JOIN resident_floors ON resident_floors.floors_id = floors.id
WHERE societies.id = 1
AND floors.status = 'Y'
AND resident_floors.floors_id IS NULL