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