On 12/11/06,
Shoaib Mir <
shoaibmir@gmail.com> wrote:
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
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==================================================================