Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id EEB529F9F46
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Dec 2006 03:03:32 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 02757-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Dec 2006 03:03:32 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from proton.planetargon.com (unknown [198.145.37.35])
	by postgresql.org (Postfix) with ESMTP id 81B559F9C35
	for <pgsql-sql@postgresql.org>; Mon, 11 Dec 2006 03:03:32 -0400 (AST)
Received: from [172.16.1.106] (unknown [61.197.227.146])
	by proton.planetargon.com (Postfix) with ESMTP id 070248045EA;
	Sun, 10 Dec 2006 23:03:30 -0800 (PST)
In-Reply-To: <bf54be870612102248j33f63ca1gd89e76d536a0262c@mail.gmail.com>
References: <bf54be870612102248j33f63ca1gd89e76d536a0262c@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <EE83C543-2CCD-41CD-8AEA-BD337673FF7B@seespotcode.net>
Cc: "Postgres SQL language list" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: subquery abnormal behavior
Date: Mon, 11 Dec 2006 16:03:27 +0900
To: "Shoaib Mir" <shoaibmir@gmail.com>
X-Mailer: Apple Mail (2.752.2)
X-Archive-Number: 200612/87
X-Sequence-Number: 27043


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