Geek asks and answers

How to rewrite the NOT IN subquery as join

Suppose the following tables in MySQL describe the 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 column identifiers are primary keys, and the F_ID column of the DOC table is a non-empty foreign key that refers to the primary key of the FOLDER table. 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 can 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')
);

'1' '2'. http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html , . , NOT IN JOINS, , . , , ?

+3
3
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
+2

:

select t.*
from t
where t.id not in (select id from s)

:

select t.*
from t left outer join
     (select distinct id from s) s
     on t.id = s.id
where s.id is null

, .

+4

If I understand your question correctly, you want to find F_IDs representing folders containing only documents until "2000-01-05", and then just

SELECT F_ID 
FROM DOC 
GROUP BY F_ID 
HAVING MAX(DATE) < '2000-01-05'
+1
source

More articles:


All Articles