Re: subquery abnormal behavior

On 12/11/06, Shoaib Mir <shoaibmir(at)gmail(dot)com> wrote:
>
> Oh that explains a lot...
>
> Thank you,
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
>
> On 12/11/06, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
> >
> >
> > On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:
> >
> > > create table myt1 (a numeric);
> > > create table myt2 (b numeric);
> > >
> > > select a from myt1 where a in (select a from myt2);
> > >
> > > This should be giving an error that column 'a' does not exist in
> > > myt2 but it runs with any error...
> >
> > The a in the IN clause is the same a in outer expression. This is in
> > effect:
> >
> > select a from myt1 where a = a;
> >
> > Now, if you were to say
> >
> > select a from myt1 where a in (select myt2.a from myt2);
> > ERROR: column myt2.a does not exist
> > LINE 1: select a from myt1 where a in (select myt2.a from myt2);
> >
> > And if you were to instead have
> > create table myt1 (a numeric);
> > CREATE TABLE
> > create table myt2 (b numeric);
> > CREATE TABLE
> > insert into myt1(a) values (1), (2);
> > INSERT 0 2
> > insert into myt2 (b) values (3), (4), (2);
> > INSERT 0 3
> > create table myt3 (a numeric);
> > CREATE TABLE
> > insert into myt3 (a) values (2), (3),(4);
> > INSERT 0 3
> > test=# select a from myt1 where a in (select a from myt3);
> > a
> > ---
> > 2
> > (1 row)
> >
> > It looks like PostgreSQL treats it as a natural join like
> >
> > select a from myt1 natural join myt3;
> >
> > Hope this helps.
> >
> > Michael Glaesemann
> > grzm seespotcode net
> >
> >
> >
>
If you want to know more about this, check into how Correlated Subqueries
work. I would never recommend using Correlated Subqueries but knowledge of
them and how/why they work helps you understand what is going on here much
better.

--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================