Można wywnioskować z odpowiedzi, które NOT IN (subquery)nie obsługują poprawnie wartości zerowych i należy ich unikać na korzyść NOT EXISTS. Taki wniosek może być jednak przedwczesny. W poniższym scenariuszu, przypisanym Chrisowi Date (programowanie i projektowanie baz danych, tom 2 nr 9, wrzesień 1989), oznacza to, NOT INże poprawnie obsługuje wartości zerowe i zwraca poprawny wynik NOT EXISTS.
Rozważ tabelę spprzedstawiającą dostawców ( sno), o których wiadomo, że dostarczają części ( pno) w ilości ( qty). Tabela zawiera obecnie następujące wartości:
VALUES ('S1', 'P1', NULL),
('S2', 'P1', 200),
('S3', 'P1', 1000)
Należy pamiętać, że ilość jest zerowa, tj. Aby móc odnotować fakt, że dostawca jest znany z dostarczania części, nawet jeśli nie wiadomo, w jakiej ilości.
Zadanie polega na znalezieniu dostawców, którzy są znanymi dostawcami o numerze katalogowym „P1”, ale nie w ilości 1000.
Następujące zastosowania NOT INdo prawidłowej identyfikacji wyłącznie dostawcy „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'
);
Jednak poniższe zapytanie wykorzystuje tę samą ogólną strukturę, ale z wynikiem, NOT EXISTSale niepoprawnie zawiera w wynikach dostawcę „S1” (tj. Dla którego ilość jest zerowa):
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
);
Więc NOT EXISTS nie jest to srebrna kula, którą mogła się pojawić!
Oczywiście źródłem problemu jest obecność wartości zerowych, dlatego „prawdziwym” rozwiązaniem jest wyeliminowanie tych wartości zerowych.
Można to osiągnąć (między innymi możliwymi projektami) za pomocą dwóch tabel:
sp dostawcy znani z dostarczania częścispq dostawcy znani z dostarczania części w znanych ilościach
Należy zauważyć, że prawdopodobnie ograniczenie klucz obcy gdzie spqreferencjesp .
Wynik można następnie uzyskać za pomocą operatora relacyjnego „minus” (będącego EXCEPTsłowem kluczowym w Standard SQL) np
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;
NOT INw szereg<> andzmian zachowania semantycznego nie w tym zestawie na coś innego?