How to rewrite a NOT IN subquery as join

join, mysql, sql, subquery

Solution

select distinct d1.F_ID
from DOC d1
left outer join (
    select F_ID
    from DOC 
    where date >= '2000-01-05'
) d2 on d1.F_ID = d2.F_ID
where d1.date < '2000-01-05' 
    and d2.F_ID is null

Problem

Let's assume that the following tables in MySQL describe documents contained in folders. ``` mysql> select * from folder; +----+----------------+ | ID | PATH | +----+----------------+ | 1 | matches/1 | | 2 | matches/2 | | 3 | shared/3 | | 4 | no/match/4 | | 5 | unreferenced/5 | +----+----------------+ mysql> select * from DOC; +----+------+------------+ | ID | F_ID | DATE | +----+------+------------+ | 1 | 1 | 2000-01-01 | | 2 | 2 | 2000-01-02 | | 3 | 2 | 2000-01-03 | | 4 | 3 | 2000-01-04 | | 5 | 3 | 2000-01-05 | | 6 | 3 | 2000-01-06 | | 7 | 4 | 2000-01-07 | | 8 | 4 | 2000-01-08 | | 9 | 4 | 2000-01-09 | | 10 | 4 | 2000-01-10 | +----+------+------------+ ``` The columns ID are the primary keys and the column F_ID of table DOC is a not-null foreign key that references the primary key of table FOLDER. By using the 'DATE' of documents in the where clause, I would like to find which folders contain only the selected documents. For documents earlier than 2000-01-05, this could be written as: ``` SELECT DISTINCT d1.F_ID FROM DOC d1 WHERE d1.DATE < '2000-01-05' AND d1.F_ID NOT IN ( SELECT d2.F_ID FROM DOC d2 WHERE NOT (d2.DATE < '2000-01-05') ); ``` and it correctly returns '1' and '2'. By reading http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html the performance for big tables could be improved if the subquery is replaced with a join. I already found questions related to NOT IN and JOINS but not exactly what I was looking for. So, any ideas of how this could be written with joins ?

Original source