Desmistificar comportamentos: NOT IN

Há uns dias, ocorreu, com um colega meu, uma situação aparentemente estranha.

Vamos ter como base as seguintes tabelas:

CREATE TABLE t1
(c11 NUMBER NOT NULL,
c12 NUMBER)
/
ALTER TABLE t1
ADD CONSTRAINT t1_pk PRIMARY KEY (c11)
/

CREATE TABLE t2
(c21 NUMBER NOT NULL,
c22 NUMBER)
/
ALTER TABLE t2
ADD CONSTRAINT t2_pk PRIMARY KEY (c21)
/

Com os seguintes dados:

INSERT INTO T1(C11, C12) VALUES(11, 1);
INSERT INTO T1(C11, C12) VALUES(12, 2);
INSERT INTO T1(C11, C12) VALUES(13, 3);
INSERT INTO T1(C11, C12) VALUES(15, 5);

INSERT INTO T2(C21, C22) VALUES(21, 1);
INSERT INTO T2(C21, C22) VALUES(22, 2);
INSERT INTO T2(C21, C22) VALUES(23, 3);
INSERT INTO T2(C21, C22) VALUES(24, 4);
INSERT INTO T2(C21, C22) VALUES(25, NULL);

A situação estranha passou-se com uma query que vou simplificar, na forma de:

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(Devemos ignorar a existência de melhores formas de atingir os objectivos desta query)

O problema é que a execução desta query resulta em.. zero resultados.
Este é, de facto, um resultado estranho; esperar-se-ia um registo: (15, 5).

Pelos vistos esta situação está documentada, pelo que, quem ler a documentação, esperará que, se na sub-query houver registos NULL, então o NOT IN retorne sempre zero registos.

Situação análoga, não aconteceria se a query fosse:

SELECT *
FROM t1
WHERE c12 IN (SELECT c22
FROM t2);

Nesta, os resultados obtidos coincidem com os esperados: ((11, 1), (12, 2), (13, 3)).

Tudo bem, mas o que interessa agora é: Porquê?

A suspeita aponta para a noção de NULL misturada com os conceitos associados à execução das operações IN e NOT IN.

Assim, temos os seguintes princípios:

- A comparação de qualquer elemento com NULL, nunca retorna TRUE em Oracle;
- Y IN (X1, X2) => Y = X1 OR Y = X2;
- Y NOT IN (X1, X2) => NOT (Y = X1 OR Y = X2) => Y <> X1 AND Y <> X2;
- X1 OR X2 = TRUE, X1 = TRUE, X2 = NULL
- X1 AND X2 = FALSE, X1 = TRUE, X2 = NULL

Ou seja, se aplicarmos os valores das querys aos princípios enumerados, temos que:
- Na versão NOT IN, basta que exista um NULL para que a condição em que ele ocorre se torne falsa, o que corresponde a um FALSE em toda a expressão (uma vez que FALSE é absorvente para o AND).
- Na versão IN, basta que exista uma condição que avalie TRUE, para que a expressão avalie TRUE também (já que TRUE é absorvente para o OR).

Assim, o resultado da query inicial

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(que é, portanto, zero resultados) deixa de ser estranho e passa a ser lógico.