NOT IN clausola e valori NULL

Questo problema è emerso quando ho ottenuto un numero di record diverso per quello che pensavo fossero query identiche, uno che utilizza un not in where vincolo e l’altro un left join . La tabella nel not in vincolo aveva un valore nullo (dati non validi) che ha causato che la query restituisse un conteggio di 0 record. Capisco perché, ma potrei usare un po ‘di aiuto per comprendere appieno il concetto.

Per dirlo semplicemente, perché la query A restituisce un risultato ma B no?

 A: select 'true' where 3 in (1, 2, 3, null) B: select 'true' where 3 not in (1, 2, null) 

Questo era su SQL Server 2005. Ho anche scoperto che chiamare set ansi_nulls off fa sì che B restituisca un risultato.

La domanda A è la stessa di:

 select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null 

Poiché 3 = 3 è vero, ottieni un risultato.

Query B è la stessa di:

 select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null 

Quando ansi_nulls è ansi_nulls , 3 <> null è SCONOSCIUTO, quindi il predicato ansi_nulls SCONOSCIUTO e non si ottengono righe.

Quando ansi_nulls è distriggersto, 3 <> null è true, quindi il predicato diventa true e ottieni una riga.

Ogni volta che usi NULL hai a che fare con una logica a tre valori.

La prima query restituisce risultati come la clausola WHERE restituisce:

  3 = 1 or 3 = 2 or 3 = 3 or 3 = null which is: FALSE or FALSE or TRUE or UNKNOWN which evaluates to TRUE 

Il secondo:

  3 <> 1 and 3 <> 2 and 3 <> null which evaluates to: TRUE and TRUE and UNKNOWN which evaluates to: UNKNOWN 

Il UNKNOWN non è lo stesso di FALSE puoi testarlo facilmente chiamando:

 select 'true' where 3 <> null select 'true' where not (3 <> null) 

Entrambe le query non ti daranno risultati

Se il UNKNOWN fosse lo stesso di FALSE, supponendo che la prima query ti avrebbe dato FALSE, il secondo avrebbe dovuto valutare TRUE come sarebbe stato lo stesso di NOT (FALSE).
Questo non è il caso.

C’è un ottimo articolo su questo argomento su SqlServerCentral .

L’intero problema di NULLs e Three-Valued Logic può essere un po ‘confuso all’inizio, ma è essenziale capire per scrivere query corrette in TSQL

Un altro articolo che raccomanderei è SQL Aggregate Functions e NULL .

Il confronto con null non è definito, a meno che non si utilizzi IS NULL.

Quindi, confrontando 3 con NULL (query A), restituisce undefined.

SELEZIONA “vero” dove 3 in (1,2, null) e SELECT “vero” dove 3 non in (1,2, null)

produrrà lo stesso risultato, poiché NOT (UNDEFINED) è ancora indefinito, ma non TRUE

NOT IN restituisce 0 record se confrontato con un valore sconosciuto

Poiché NULL è uno sconosciuto, una query NOT IN contiene un NULL o NULL nella lista di valori possibili restituirà sempre 0 record poiché non c’è modo di essere sicuri che il valore NULL non sia il valore testato.

Il titolo di questa domanda al momento della stesura è

SQL NOT IN vincolo e valori NULL

Dal testo della domanda sembra che il problema si sia verificato in una query SQL DML SELECT anziché in un DDL SQL CONSTRAINT .

Tuttavia, soprattutto data la formulazione del titolo, voglio sottolineare che alcune affermazioni fatte qui sono affermazioni potenzialmente fuorvianti, quelle sulla falsariga di (parafrasi)

Quando il predicato viene valutato su UNKNOWN, non si ottengono righe.

Sebbene questo sia il caso di SQL DML, quando si considerano i vincoli l’effetto è diverso.

Considera questa tabella molto semplice con due vincoli presi direttamente dai predicati nella domanda (e indirizzati in una risposta eccellente da @Brannon):

 DECLARE @T TABLE ( true CHAR(4) DEFAULT 'true' NOT NULL, CHECK ( 3 IN (1, 2, 3, NULL )), CHECK ( 3 NOT IN (1, 2, NULL )) ); INSERT INTO @T VALUES ('true'); SELECT COUNT(*) AS tally FROM @T; 

Come per la risposta di @ Brannon, il primo vincolo (utilizzando IN ) viene valutato su TRUE e il secondo vincolo (utilizzando NOT IN ) viene valutato su UNKNOWN. Tuttavia , l’inserto ha successo! Pertanto, in questo caso non è strettamente corretto dire “non si ottengono righe” perché in effetti abbiamo inserito una riga come risultato.

L’effetto sopra riportato è effettivamente quello corretto per quanto riguarda lo standard SQL-92. Confrontate e confrontate la seguente sezione con le specifiche SQL-92

7.6 dove clausola

Il risultato è una tabella di quelle righe di T per le quali il risultato della condizione di ricerca è vero.

4.10 Vincoli di integrità

Un vincolo di controllo tabella è soddisfatto se e solo se la condizione di ricerca specificata non è falsa per qualsiasi riga di una tabella.

In altre parole:

In SQL DML, le righe vengono rimosse dal risultato quando WHERE restituisce UNKNOWN perché non soddisfa la condizione “è vero”.

In SQL DDL (cioè i vincoli), le righe non vengono rimosse dal risultato quando valutano in UNKNOWN perché soddisfano la condizione “non è false”.

Sebbene gli effetti in SQL DML e SQL DDL possano sembrare contraddittori, vi è una ragione pratica per dare a UNKNOWN risultati come “beneficio del dubbio” permettendo loro di soddisfare un vincolo (più correttamente, permettendo loro di non riuscire a soddisfare un vincolo) : senza questo comportamento, ogni vincolo dovrebbe gestire in modo esplicito i null e ciò sarebbe molto insoddisfacente dal punto di vista del design del linguaggio (per non parlare del dolore giusto per i programmatori!)

ps se stai trovando difficile seguire una logica come “lo sconosciuto non riesce a soddisfare un vincolo”, come lo sono io per scrivere, quindi considera di poter fare a meno di tutto questo semplicemente evitando colonne nullable nel DDL SQL e qualsiasi cosa in SQL DML che produce valori nulli (ad es. Join esterni)!

In A, 3 viene testato per l’uguaglianza rispetto a ciascun membro del set, cedendo (FALSE, FALSE, TRUE, UNKNOWN). Poiché uno degli elementi è TRUE, la condizione è TRUE. (È anche ansible che qui si verifichi un cortocircuito, quindi in realtà si interrompe non appena raggiunge il primo TRUE e non valuta mai 3 = NULL.)

In B, penso che stia valutando la condizione come NOT (3 in (1,2, null)). Test 3 per l’uguaglianza rispetto ai rendimenti impostati (FALSE, FALSE, UNKNOWN), che viene aggregato a UNKNOWN. NON (SCONOSCIUTO) produce SCONOSI. Quindi, in generale, la verità della condizione è sconosciuta, che alla fine è trattata essenzialmente come FALSE.

Null significa e assenza di dati, cioè è sconosciuto, non un valore di dati di niente. È molto facile per le persone provenienti da uno sfondo di programmazione confondere questo perché nei linguaggi di tipo C quando si usano i puntatori null non è davvero nulla.

Quindi nel primo caso 3 è effettivamente nell’insieme di (1,2,3, null) così viene restituito true

Nel secondo tuttavia è ansible ridurlo a

seleziona ‘true’ dove 3 not in (null)

Quindi non viene restituito nulla perché il parser non sa nulla del set a cui lo si sta confrontando – non è un set vuoto ma un set sconosciuto. Usare (1, 2, null) non aiuta perché il set (1,2) è ovviamente falso, ma lo stai facendo e lo stai contro lo sconosciuto, che è sconosciuto.

Se si desidera filtrare con NOT IN per una sottoquery contenente NULL, basta selezionare non null

 SELECT blah FROM t WHERE blah NOT IN (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL ) 

Si può concludere dalle risposte qui che NOT IN (subquery) non gestisce correttamente i null e dovrebbe essere evitato a favore di NOT EXISTS . Tuttavia, una tale conclusione potrebbe essere prematura. Nel seguente scenario, accreditato a Chris Date (Database Programming and Design, Vol 2 No 9, September 1989), NOT IN è NOT IN che gestisce i valori null correttamente e restituisce il risultato corretto, piuttosto che NOT EXISTS .

Si consideri una tabella sp per rappresentare i fornitori ( sno ) che sono noti per fornire parti ( pno ) in quantità ( qty ). La tabella contiene attualmente i seguenti valori:

  VALUES ('S1', 'P1', NULL), ('S2', 'P1', 200), ('S3', 'P1', 1000) 

Si noti che la quantità è nullable cioè per poter registrare il fatto che un fornitore è noto per fornire parti anche se non è noto in quale quantità.

Il compito è di trovare i fornitori che sono noti forniscono il numero di parte “P1” ma non in quantità di 1000.

I seguenti usi NOT IN per identificare correttamente solo il fornitore ‘S2’:

 WITH sp AS ( SELECT * FROM ( VALUES ( 'S1', 'P1', NULL ), ( 'S2', 'P1', 200 ), ( 'S3', 'P1', 1000 ) ) AS T ( sno, pno, qty ) ) SELECT DISTINCT spx.sno FROM sp spx WHERE spx.pno = 'P1' AND 1000 NOT IN ( SELECT spy.qty FROM sp spy WHERE spy.sno = spx.sno AND spy.pno = 'P1' ); 

Tuttavia, la query seguente utilizza la stessa struttura generale ma con NOT EXISTS ma include erroneamente il fornitore ‘S1’ nel risultato (ovvero per il quale la quantità è nulla):

 WITH sp AS ( SELECT * FROM ( VALUES ( 'S1', 'P1', NULL ), ( 'S2', 'P1', 200 ), ( 'S3', 'P1', 1000 ) ) AS T ( sno, pno, qty ) ) SELECT DISTINCT spx.sno FROM sp spx WHERE spx.pno = 'P1' AND NOT EXISTS ( SELECT * FROM sp spy WHERE spy.sno = spx.sno AND spy.pno = 'P1' AND spy.qty = 1000 ); 

Quindi NOT EXISTS non è il proiettile d’argento che potrebbe essere apparso!

Naturalmente, la fonte del problema è la presenza di null, quindi la soluzione “reale” è quella di eliminare quei valori nulli.

Questo può essere ottenuto (tra gli altri possibili progetti) usando due tabelle:

  • fornitori sp noti per fornire parti
  • fornitori spq noti per fornire parti in quantità note

notando che ci dovrebbe probabilmente essere un vincolo di chiave esterna in cui spq riferimento a sp .

Il risultato può quindi essere ottenuto utilizzando l’operatore relazionale ‘minus’ (essendo la parola chiave EXCEPT in SQL standard) ad es

 WITH sp AS ( SELECT * FROM ( VALUES ( 'S1', 'P1' ), ( 'S2', 'P1' ), ( 'S3', 'P1' ) ) AS T ( sno, pno ) ), spq AS ( SELECT * FROM ( VALUES ( 'S2', 'P1', 200 ), ( 'S3', 'P1', 1000 ) ) AS T ( sno, pno, qty ) ) SELECT sno FROM spq WHERE pno = 'P1' EXCEPT SELECT sno FROM spq WHERE pno = 'P1' AND qty = 1000; 

questo è per Boy:

 select party_code from abc as a where party_code not in (select party_code from xyz where party_code = a.party_code); 

questo funziona indipendentemente dalle impostazioni ansi

anche questo potrebbe essere utile per conoscere la differenza logica tra join, exists e in http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx