SQL Server and Oracle – Understanding NULL NOT IN When Set is Empty

nulloraclesql server

The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle).

I have the following SQL:

SELECT 1
FROM dual
WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

which returns an empty set and 1 respectively. When we substitute some other value for NULL, we got the exact same result. 

SELECT 1
FROM dual
WHERE 33 IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE 33 NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

So how does the comparison between NULL and the empty result set work here?

Best Answer

Lets see what happens with these two conditions:

WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

since the subquery yields an empty table, we can write them in pseudocode:

WHERE NULL IN (<empty_table>);

WHERE NULL NOT IN (<empty_table>);

So the first asks whether some (NULL) value is IN an empty table. The result of such condition is always FALSE, independent on whether the value is null or not, since the table is empty.

Applying the same reasoning in the second condition, it is always TRUE, again independent of the value.

It's easy to confirm in Postgres what the result value of these conditions are, as we have a boolean type. See dbfiddle.uk, where the first shows f (FALSE) and the second shows t (TRUE).

As a result of the above, when you run your two queries, they become:

SELECT 1
FROM dual
WHERE FALSE ;

giving an empty result (correct, since the WHERE condition is FALSE)
and

SELECT 1
FROM dual
WHERE TRUE ;

giving one row (correct again).

Related Question