Home > SQL > An Interesting Feature of NOT IN and Multi-Row Subqueries

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
About these ads
Categories: SQL Tags: ,
  1. Sunil Kumar Mahalik
    March 22, 2013 at 3:04 pm

    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.

  2. March 3, 2013 at 3:09 pm

    Interesting and something to keep in mind. Thanks for sharing

  3. March 2, 2013 at 9:55 am

    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.

  4. Andrew Tulley
    March 4, 2013 at 1:31 pm

    Gary,

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

    Andy

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: