Wartość NULL i podzapytania, a operator IN i NOT IN

Często pewną konsternacje budzi problem wynikający z jednoczesnego stosowania wartości NULL i operatora sprawdzenia czy elementu nie ma na liście (NOT IN) co prowadzi do uzyskania w wyniku pustego zestawu rekordów. NULL dla operatora IN działa poprawnie, a dla operatora NOT IN nie działa! Czy na pewno takie zachowanie jest niepoprawne?

Stwórzmy przykładową tabelkę do testów:


CREATE TABLE test (ID NUMBER);

INSERT INTO test VALUES (6);


Wtedy takie zapytanie:

select id from test where id NOT IN (1,2,3);
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
+----+


zwraca rekordy których ID nie znajduje się na liście, ale już takie zapytanie:


select id from test where id NOT IN (1,2,3,NULL);
Empty set (0.00 sec)


nie zwraca żadnego wiersza. A zapytanie:


select id from test where id IN (1,2,3,NULL);
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+


też zwraca dane. Zatem NULL dla operatora IN nie powoduje “zepsucia” wyników, ale dla NOT IN zachowanie to może budzić u początkującego użytkownika SQL konsternacje.

Jest to jednak zachowanie poprawne, które wynika ze znaczenia wartości NULL. Pamiętajmy bowiem, że wartość NULL nie jest zwykłą, konkretną wartością, a jedynie symbolicznym zapisem wartości nieznanej. Najwygodniej to zrozumieć czytając NULL jako NIEWIADOMOCO.

W pierwszym przypadku NOT IN (1,2,3) o każdej ze zwróconych wartości można powiedzieć, że jej nie ma na liście i sprawa jest oczywista.

Drugi przypadek NOT IN (1,2,3,NULL) natomiast można wyjaśnić następująco. Na testowanej liście znajduje się 1,2,3 i NIEWIADOMOCO. Skoro jest tam NIEWIADOMOCO, to o żadnej wartości nie można powiedzieć w 100%, że jej nie ma na tej liście, bo to NIEWIADOMOCO może być przecież dowolną wartością. Ponieważ dla żadnego rekordu baza nie jest w stanie określić wyniku – nie zwraca więc niczego.

NULL na liście nie “psuje” trzeciego przypadku IN (1,2,3,NULL) ponieważ o każdej ze zwróconych wartości można przecież z całą pewnością powiedzieć, że znajduje się na liście. Jest tam też NIEWIADOMOCO, ale skoro nie wiadomo co – baza nie zwraca więcej rekordów. Te których jest jednak pewna (wynikiem warunku jest TRUE) – zwraca.

Na koniec dla uściślenia: w drugim przypadku dla każdego rekordu wynikiem sprawdzenia warunku NOT IN (1,2,3,NULL) jest “nie wiadomo” – w bazach danych mamy bowiem do czynienia z logiką trójstanową – wynikiem wyrażenia logicznego może być PRAWDA, FAŁSZ oraz NIEWIADOMO.

Wydaje się, że opisywany problem jest rzadki bo mało kto celowo dopisuje na liście po operatorze NOT IN wartość NULL. Nic bardziej mylnego. Pamiętajmy bowiem, że testowanie operatorem NOT IN nie musi dotyczyć konkretnej wartości, a wyniku podzapytania:

Mając taką tabelę:


CREATE TABLE test2 (ID2 NUMBER);

INSERT INTO test2 VALUES (3);


Piszemy zapytanie:


SELECT * FROM test WHERE ID NOT IN (SELECT ID2 FROM TEST2);


Zadowoleni, że działa poprawnie jedziemy na długi urlop, a w międzyczasie ktoś dodaje jeden malutki NULL do tabeli:


INSERT INTO test2 VALUES (NULL);


Ponowne wywołanie


SELECT * FROM test WHERE ID NOT IN (SELECT ID2 FROM TEST2);


Efekt? Raporty się nie generują, nikt nie wie jak temu zaradzić, szef nas przywraca przymusowo do pracy (bezpośrednio z plaży!). Ogólnie – rozpacz! Pamiętajmy zatem, że jeżeli używamy operatora NOT IN z podzapytaniem to upewnijmy się, że kolumna przez nie zwracana jest NOT NULL, albo dodajmy dla pewności warunek:


SELECT * FROM test WHERE ID NOT IN (SELECT ID FROM TEST2 WHERE ID2 IS NOT NULL);