Re: subquery abnormal behavior - Mailing list pgsql-sql

From Shoaib Mir
Subject Re: subquery abnormal behavior
Date
Msg-id bf54be870612102324i50635183s8dab2f3af55f5cfc@mail.gmail.com
Whole thread Raw
In response to Re: subquery abnormal behavior  (Michael Glaesemann <grzm@seespotcode.net>)
Responses Re: subquery abnormal behavior
List pgsql-sql
Oh that explains a lot...

Thank you,
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)


On 12/11/06, Michael Glaesemann <grzm@seespotcode.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



pgsql-sql by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: subquery abnormal behavior
Next
From: "Aaron Bono"
Date:
Subject: Re: subquery abnormal behavior