From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Shoaib Mir" <shoaibmir(at)gmail(dot)com> |
Cc: | "Postgres SQL language list" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: subquery abnormal behavior |
Date: | 2006-12-11 07:03:27 |
Message-ID: | EE83C543-2CCD-41CD-8AEA-BD337673FF7B@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shoaib Mir | 2006-12-11 07:24:29 | Re: subquery abnormal behavior |
Previous Message | Shoaib Mir | 2006-12-11 06:57:11 | Re: subquery abnormal behavior |