Re: understanding NOT IN / IN

From: rgvguplb <rgvguplb_at_gmail.com>
Date: Tue, 17 Feb 2009 11:21:49 -0800 (PST)
Message-ID: <bb1e2a85-3b85-4b7d-97b2-02333d2e6c6d_at_n21g2000vba.googlegroups.com>

On Feb 17, 10:41 am, 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'm guessing the org_id in your subquery refers to org_id in the outer query. stick a b. before it and it won't run. you may not want it that way, but that's probably what it's doing. Received on Tue Feb 17 2009 - 13:21:49 CST