Re: understanding NOT IN / IN

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 17 Feb 2009 11:16:40 -0800 (PST)
Message-ID: <cb829acb-5fcf-4ca7-9976-558b036f5782_at_f24g2000vbf.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

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

Received on Tue Feb 17 2009 - 13:16:40 CST