Re: understanding NOT IN / IN

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 17 Feb 2009 11:45:14 -0800 (PST)
Message-ID: <c5222c81-9684-42c1-9d0a-1606703689b0_at_q30g2000vbn.googlegroups.com>

On Feb 17, 2:27 pm, Tony Sequeira <nob..._at_127.0.0.1> wrote:
> 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
> ++- Hide quoted text -
>
> - Show quoted text -

Yes, it is based on table column scope. Instead of being b.org_id as you would think from reading the subquery it is a.org_id. A good reason to always alias your tables and prefix the alias to your column in all sub-queries.

HTH -- Mark D Powell -- Received on Tue Feb 17 2009 - 13:45:14 CST