2011-02-09

not exists null

I wrote earlier about not in and null. Be careful also when using not exists predicate together with a sub query resulting nulls.


SQL> select 1 from dual where not exists (select 1 from dual where 1=0);

1
----------
1

SQL> select 1 from dual where 1=0;

no rows selected



SQL> select 1 from dual where not exists (select max(1) from dual where 1=1);

no rows selected

SQL> select max(1) from dual where 1=1;

MAX(1)
----------
1



SQL> select 1 from dual where not exists (select max(1) from dual where 1=0);

no rows selected

SQL> select max(1) from dual where 1=0;

MAX(1)
----------


SQL> select 1 from dual where not exists (select null from dual);

no rows selected

SQL> select null from dual;

N
-



In the sub query there is a row but it is null, unknown. So not exists null evaluates to false. More about the issue in responses to a oracle-l mailing list post.

2 comments:

  1. even
    EXISTS (SELECT 1/0 FROM DUAL)

    this is internal tuning ;)

    ReplyDelete
  2. SQL> select 1 from dual where exists (select 1/0 from dual);

    1
    ----------
    1

    SQL> select 1 from dual where not exists (select 1/0 from dual);

    no rows selected

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.