Re: understanding NOT IN / IN
Date: Tue, 17 Feb 2009 11:45:31 -0800 (PST)
Message-ID: <9e26939b-1547-434b-8d1c-d65c25979e93_at_b40g2000pri.googlegroups.com>
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
I think that you have the answer already.
Correlated Subqueries
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_10002.htm#i2066912
select * from a
where a.org_id not in (select org_id from b);
is actually:
select * from a
where a.org_id in (select A.ORG_ID from b);
Essentially, the where clause is saying: For the current row in A, is A.ORG_ID equal to one of the two same values of A.ORG_ID (the value from the current row in A) returned for each of the two rows in B? Unless A.ORG_ID is NULL, the result will always be TRUE.
The reason for the ORA-00904 is because table B only has a single column named NAME.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Feb 17 2009 - 13:45:31 CST