Re: understanding NOT IN / IN

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 17 Feb 2009 20:46:58 +0100
Message-ID: <499B1432.5040805_at_gmail.com>

ciapecki schrieb:
> Following situation:
>
> create table a (org_id number, org_name varchar2(1));
> insert into a values(1,'a');
> insert into a values(2,'b');
> insert into a values(3,'c');
>
> create table b (name varchar2(1));
> insert into b values('a');
> insert into b values('b');
>
> create table c (org_id number, org_name varchar2(1));
> insert into c values(2,'b');
> commit;
>
> select * from a
> where a.org_id not in (select org_id from b);
> -- returns 0 rows
>
> select * from a
> where a.org_id in (select org_id from b);
> -- returns all 3 rows from a
>
> Why does it actually work?
>
> there is no org_id in table b.
> when you run
> select org_id from b
> *
> ORA-00904: "ORG_ID": invalid identifier
>
> thanks,
> chris

You have to lookup in the documentation for better understanding. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries007.htm#i2067858

The scope of visibility of outer query is expanded into the inner query, it can go as far as nesting allows ( in this case by 10gR2 up to 255 levels).

To demonstrate, that nested subquery with 2 levels of nesting knows about columns of most outer query - very trivial example:

SQL> select *

   2 from a a_outer where exists (
   3 select null

   4      from b b_middle
   5      where exists (
   6        select null
   7          from  c c_inner
   8          where a_outer.org_id = 2
   9          ));

     ORG_ID O
---------- -
          2 b

Now, to explain your observed behaviour, simply rewrite your queries as

select * from a a_outer
where a_outer.org_id not in (select a_outer.org_id from b); -- returns 0 rows

select * from a a_outer
where a_outer.org_id in (select a_outer.org_id from b); -- returns all 3 rows from a

so, i think, the result is obvious.

Best regards

Maxim
(it's a kind of dejavu, iirc, some years ago we had with Michel Cadot a topic on the very same newsgroup, where we discussed nested subqueries ;-) ) Received on Tue Feb 17 2009 - 13:46:58 CST