> 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