On 12/11/06, Shoaib Mir <[EMAIL PROTECTED]> wrote:
Oh that explains a lot... Thank you, ------------- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/11/06, Michael Glaesemann <[EMAIL PROTECTED]> 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 ==================================================================
