MySQL: From sub query to a single query

Question

I have this query which i believe can be optimized:

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

Is this query fine to use or there it can be improved..?

Answer 1

It looks like you want to get all floors that aren't present in resident_floors. For this we can left join RF in and ask for only rows where the join failed resulting in a null in 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