I need to select all records from a table containing id's that are not "checked" in four other tables. Here's my query, which works:
select id, idDate, idInfo, idChecked from aTable
where id not in (select id from aSimilarTable1 where idChecked is not null)
and id not in (select id from aSimilarTable2 where idChecked is not null)
and id not in (select id from aSimilarTable3 where idChecked is not null)
and id not in (select id from aSimilarTable4 where idChecked is not null)
The tables grow over time, and now this query takes a very long time to run (several minutes, at best). The size of the tables are the following:
aTable - 1000 records
aSimilarTable1, 2, 3, 4 - 50,000 records
I will work on reducing the size of the tables. However, is there a more efficient way to make the above query?
--CLARIFICATION--
Not all id's from aTable may be present in aSimilarTable1,2,3 or 4. I am looking for ids in aTable that are either not present in any aSimilarTable, or if present, are not "checked".
--UPDATE--
Explain plan for the query:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY aTable ALL null null null null 796 Using where
5 DEPENDENT SUBQUERY aSimilarTable4 ALL null null null null 21217 Using where
4 DEPENDENT SUBQUERY aSimilarTable3 ALL null null null null 59077 Using where
3 DEPENDENT SUBQUERY aSimilarTable2 ALL null null null null 22936 Using where
2 DEPENDENT SUBQUERY aSimilarTable1 ALL null null null null 49734 Using where
Use LEFT JOIN's.
SELECT a.id, a.idDate, a.idInfo, a.idChecked
FROM aTable a
LEFT JOIN aSimilarTable1 b ON a.id = b.id
LEFT JOIN aSimilarTable2 c ON a.id = c.id
LEFT JOIN aSimilarTable3 d ON a.id = d.id
LEFT JOIN aSimilarTable4 e ON a.id = e.id