MySQL “NOT IN” query

Volevo eseguire una query semplice per generare tutte le righe di Table1 cui un valore di colonna principale non è presente in una colonna in un’altra tabella ( Table2 ).

Ho provato a usare:

 SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal 

Questo invece genera un errore di syntax. La ricerca su Google mi ha portato a forum in cui la gente diceva che MySQL non supporta NOT IN e che è necessario utilizzare qualcosa di estremamente complesso. È vero? O sto facendo un errore orrendo?

Per usare IN, devi avere un set, usa questa syntax invece:

 SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2) 

L’opzione subquery ha già avuto risposta, ma nota che in molti casi un LEFT JOIN può essere un modo più veloce per fare ciò:

 SELECT table1.* FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal WHERE table2.principal IS NULL 

Se vuoi controllare più tabelle per assicurarti che non sia presente in nessuna delle tabelle (come nel commento di SRKR), puoi usare questo:

 SELECT table1.* FROM table1 LEFT JOIN table2 ON table2.name=table1.name LEFT JOIN table3 ON table3.name=table1.name WHERE table2.name IS NULL AND table3.name IS NULL 

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL in MySQL

MySQL, così come tutti gli altri sistemi eccetto SQL Server, è in grado di ottimizzare LEFT JOIN / IS NULL per restituire FALSE non appena viene trovato il valore corrispondente, ed è l’unico sistema che si preoccupa di documentare questo comportamento. […] Dal momento che MySQL non è in grado di utilizzare gli algoritmi di join HASH e MERGE , l’unico ANTI JOIN cui è capace è il NESTED LOOPS ANTI JOIN

[…]

Essenzialmente, [ NOT IN ] è esattamente lo stesso piano utilizzato da LEFT JOIN / IS NULL , nonostante questi piani siano eseguiti dai diversi rami di codice e abbiano un aspetto diverso nei risultati di EXPLAIN . Gli algoritmi sono infatti gli stessi di fatto e le query sono complete nello stesso tempo.

[…]

È difficile dire il motivo esatto della [riduzione delle prestazioni quando si usa NOT EXISTS ] , poiché questa caduta è lineare e non sembra dipendere dalla distribuzione dei dati, dal numero di valori in entrambe le tabelle ecc., Purché entrambi i campi siano indicizzati. Dato che in MySQL ci sono tre parti di codice che essenzialmente fanno un lavoro, è ansible che il codice responsabile di EXISTS faccia qualche tipo di controllo extra che richiede più tempo.

[…]

MySQL è in grado di ottimizzare tutti e tre i metodi per fare una sorta di NESTED LOOPS ANTI JOIN . […] Tuttavia, questi tre metodi generano tre piani diversi che vengono eseguiti da tre diversi pezzi di codice. Il codice che esegue il predicato EXISTS è circa il 30% meno efficiente […]

Ecco perché il modo migliore per cercare valori mancanti in MySQL è usare LEFT JOIN / IS NULL o NOT IN piuttosto che NOT EXISTS .

(sottolinea aggiunto)

Sfortunatamente sembra essere un problema con l’uso di MySql della clausola “NOT IN”, lo screenshot qui sotto mostra l’opzione sub-query che restituisce risultati errati:

 mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 1.1.8 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.21 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 7 rows in set (0.07 sec) mysql> select count(*) from TABLE_A where TABLE_A.Pkey not in (select distinct TABLE_B.Fkey from TABLE_B ); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.07 sec) mysql> select count(*) from TABLE_A left join TABLE_B on TABLE_A.Pkey = TABLE_B.Fkey where TABLE_B.Pkey is null; +----------+ | count(*) | +----------+ | 139 | +----------+ 1 row in set (0.06 sec) mysql> select count(*) from TABLE_A where NOT EXISTS (select * FROM TABLE_B WHERE TABLE_B.Fkey = TABLE_A.Pkey ); +----------+ | count(*) | +----------+ | 139 | +----------+ 1 row in set (0.06 sec) mysql> 

Attenzione! NOT IN non è un alias per <> ANY , ma per <> ALL !

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

 SELECT c FROM t1 LEFT JOIN t2 USING (c) WHERE t2.c IS NULL 

non può essere sostituito da

 SELECT c FROM t1 WHERE c NOT IN (SELECT c FROM t2) 

Devi usare

 SELECT c FROM t1 WHERE c <> ANY (SELECT c FROM t2)