Re: understanding NOT IN / IN

From: Tony Sequeira <nobody_at_127.0.0.1>
Date: Tue, 17 Feb 2009 19:27:30 +0000
Message-ID: <499b0fa2$0$16160$db0fefd9_at_news.zen.co.uk>

Mark D Powell wrote:

> On Feb 17, 1:41 pm, ciapecki <ciape..._at_gmail.com> wrote:

>> 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

> 
> Chris, I can duplicate your results on 9.2.0.6 and 10.2.0.3 but I do
> not know why a syntax error is not reported.  I will have to poke
> around to see why I discover.
> 
> -- Mark D Powell --
> 

Surely the org_id in the sub-query is correlated, i.e. is a.org.id?

I see no reason for a syntax error.

-- 
S. Anthony Sequeira
++
Prediction is very difficult, especially of the future.
		-- Niels Bohr
++

Received on Tue Feb 17 2009 - 13:27:30 CST