## An Interesting Feature of NOT IN and Multi-Row Subqueries

Take the following simple SQL statement:

SELECT * FROM dual WHERE 'x' NOT IN (SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

SQL> SELECT * 2 FROM dual 3 WHERE 'x' NOT IN 4 (SELECT 'a' FROM dual); D - X

What about in the following case?

SELECT * FROM dual WHERE 'x' NOT IN (SELECT 'a' FROM dual UNION ALL SELECT NULL FROM dual);

You might expect this to return a row as well since ‘x’ is still not in our subquery (which now returns two values: ‘x’ and NULL).

You’d be wrong, however. This query will in fact return no rows:

SQL> SELECT * 2 FROM dual 3 WHERE 'x' NOT IN 4 (SELECT 'a' FROM dual 5 UNION ALL 6 SELECT NULL FROM dual); no rows selected

If the subquery referenced by your NOT IN statement contains any NULL values, the NOT IN condition will evaluate to unknown.

It’s worth noting that this is not the case if you use an IN as opposed to a NOT IN:

SQL> SELECT * 2 FROM dual 3 WHERE 'a' IN 4 (SELECT 'a' FROM dual 5 UNION ALL 6 SELECT NULL FROM dual); D - X

Hi Andy,

i am confused by using “select empno,sal from emp where empno not in(7369,7499,null)” its showing “no rows selected” but when using with check constraint it restrict 7369 and 7499 values and allow other values but why not its working same for the select command given above.

Interesting and something to keep in mind. Thanks for sharing

Technically a NOT IN (‘A’,null) doesn’t evaluate to false, but to unknown. You can see the difference with a check constraint

create table test_c (val varchar2(3) constraint v_ck check (val not in (‘A’,null)));

insert into test_c (val) values (‘B’);

It passes because the NOT IN returns unknown rather than false.

Gary,

Thanks for your comment and a good point. I’ve now modded the original post.

Andy