Re: understanding NOT IN / IN

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 17 Feb 2009 22:49:06 -0600
Message-ID: <crMml.7367$jZ1.441_at_flpi144.ffdc.sbc.com>

Maxim Demenko wrote:
> 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
> ;-) )

I guess I never run into this because I never assume the db engine is going to do what *I* think it should - and I try to always select my columns explicitly. eg: tbl.column_name Received on Tue Feb 17 2009 - 22:49:06 CST