SQL “select where not in subquery” non restituisce risultati

Disclaimer: ho capito il problema (credo), ma volevo aggiungere questo problema a Stack Overflow poiché non riuscivo (facilmente) a trovarlo da nessuna parte. Inoltre, qualcuno potrebbe avere una risposta migliore di me.

Ho un database in cui una tabella “Comune” è referenziata da diverse altre tabelle. Volevo vedere quali record nella tabella comune erano rimasti orfani (cioè non avevano riferimenti da nessuna delle altre tabelle).

Ho eseguito questa query:

select * from Common where common_id not in (select common_id from Table1) and common_id not in (select common_id from Table2) 

So che ci sono record orfani, ma non è stato restituito alcun record. Perchè no?

(Questo è SQL Server, se è importante).

Aggiornare:

Questi articoli nel mio blog descrivono le differenze tra i metodi in modo più dettagliato:

  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : SQL Server
  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : PostgreSQL
  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : Oracle
  • NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : MySQL

Esistono tre modi per eseguire una query di questo tipo:

  • LEFT JOIN / IS NULL :

     SELECT * FROM common LEFT JOIN table1 t1 ON t1.common_id = common.common_id WHERE t1.common_id IS NULL 
  • NOT EXISTS :

     SELECT * FROM common WHERE NOT EXISTS ( SELECT NULL FROM table1 t1 WHERE t1.common_id = common.common_id ) 
  • NOT IN :

     SELECT * FROM common WHERE common_id NOT IN ( SELECT common_id FROM table1 t1 ) 

Quando table1.common_id non è annullabile, tutte queste query sono semanticamente uguali.

Quando è nullable, NOT IN è diverso, poiché IN (e, quindi, NOT IN ) restituisce NULL quando un valore non corrisponde a nulla in una lista che contiene un valore NULL .

Ciò potrebbe creare confusione, ma potrebbe diventare più ovvio se ricordiamo la syntax alternativa per questo:

 common_id = ANY ( SELECT common_id FROM table1 t1 ) 

Il risultato di questa condizione è un prodotto booleano di tutti i confronti all’interno dell’elenco. Ovviamente, un singolo valore NULL produce il risultato NULL che rende anche l’intero risultato NULL .

Non possiamo mai dire definitivamente che common_id non è uguale a nulla da questo elenco, poiché almeno uno dei valori è NULL .

Supponiamo di avere questi dati:

 common -- 1 3 table1 -- NULL 1 2 

LEFT JOIN / IS NULL e NOT EXISTS restituiranno 3 , NOT IN non restituirà nulla (dal momento che valuterà sempre FALSE o NULL ).

In MySQL , nel caso di colonne non annullabili, LEFT JOIN / IS NULL e NOT IN sono un po ‘(più%) più efficienti di NOT EXISTS . Se la colonna è nullable, NOT EXISTS è la più efficiente (di nuovo, non molto).

In Oracle , tutte e tre le query producono gli stessi piani (un ANTI JOIN ).

In SQL Server , NOT IN / NOT EXISTS sono più efficienti, poiché LEFT JOIN / IS NULL non può essere ottimizzato per un ANTI JOIN dal suo ottimizzatore.

In PostgreSQL , LEFT JOIN / IS NULL e NOT EXISTS sono più efficienti di NOT IN , sono ottimizzati per un Anti Join , mentre NOT IN usa il hashed subplan (o anche un subplan semplice se la sottoquery è troppo grande per hash)

Se si desidera che il mondo sia un luogo booleano a due valori, è necessario impedire il caso null (terzo valore).

Non scrivere clausole IN che consentano null nel lato elenco. Filtrale fuori!

 common_id not in ( select common_id from Table1 where common_id is not null ) 

Table1 o Table2 ha alcuni valori nulli per common_id. Utilizza invece questa query:

 select * from Common where common_id not in (select common_id from Table1 where common_id is not null) and common_id not in (select common_id from Table2 where common_id is not null) 
 select * from Common c where not exists (select t1.commonid from table1 t1 where t1.commonid = c.commonid) and not exists (select t2.commonid from table2 t2 where t2.commonid = c.commonid) 
 SELECT T.common_id FROM Common T LEFT JOIN Table1 T1 ON T.common_id = T1.common_id LEFT JOIN Table2 T2 ON T.common_id = T2.common_id WHERE T1.common_id IS NULL AND T2.common_id IS NULL 

Appena fuori dalla mia testa …

 select c.commonID, t1.commonID, t2.commonID from Common c left outer join Table1 t1 on t1.commonID = c.commonID left outer join Table2 t2 on t2.commonID = c.commonID where t1.commonID is null and t2.commonID is null 

Ho fatto alcuni test e qui i miei risultati sono stati la risposta di @ patmortech e i commenti di @ rexem.

Se Table1 o Table2 non sono indicizzati su commonID, si ottiene una scansione della tabella ma la query di @ patmortech è ancora due volte più veloce (per una tabella master di 100.000 righe).

Se nessuno dei due è indicizzato su commonID, si ottengono due scansioni della tabella e la differenza è trascurabile.

Se entrambi sono indicizzati su commonID, la query “not exists” viene eseguita in 1/3 del tempo.

Supponiamo che questi valori per common_id:

 Common - 1 Table1 - 2 Table2 - 3, null 

Vogliamo che la riga in Common restituisca, perché non esiste in nessuna delle altre tabelle. Tuttavia, il nulla getta in una chiave inglese.

Con questi valori, la query è equivalente a:

 select * from Common where 1 not in (2) and 1 not in (3, null) 

Questo è equivalente a:

 select * from Common where not (1=2) and not (1=3 or 1=null) 

Questo è dove inizia il problema. Quando si confronta con un nullo, la risposta è sconosciuta . Quindi la query si riduce a

 select * from Common where not (false) and not (false or unkown) 

falso o sconosciuto è sconosciuto:

 select * from Common where true and not (unknown) 

vero e non sconosciuto è anche sconosciuto:

 select * from Common where unknown 

La condizione where non restituisce record in cui il risultato è sconosciuto, quindi non otteniamo alcun record.

Un modo per risolvere questo problema è utilizzare l’operatore exists anziché in. Exists non restituisce mai unkown perché opera su righe anziché colonne. (Esiste una riga o no, nessuna di queste ambiguità nulle a livello di riga!)

 select * from Common where not exists (select common_id from Table1 where common_id = Common.common_id) and not exists (select common_id from Table2 where common_id = Common.common_id) 

questo ha funzionato per me 🙂

seleziona * da Comune

dove

common_id not in (selezionare ISNULL (common_id, ‘dummy-data’) da Table1)

e common_id non in (selezionare ISNULL (common_id, ‘dummy-data’) da Table2)

 select *, (select COUNT(ID) from ProductMaster where ProductMaster.CatID = CategoryMaster.ID) as coun from CategoryMaster