Hello,
Can someone please explain to me why my first query does not return an error?
--
subquerytest=# create table something (id bigint generated always as identity primary key);
CREATE TABLE
subquerytest=# create table somethingelse (asdf text);
CREATE TABLE
subquerytest=# select * from something where id in (select id from somethingelse); id
----
(0 rows)
subquerytest=# select id from somethingelse;
ERROR: column "id" does not exist
LINE 1: select id from somethingelse; ^
subquerytest=# select * from something where id in (select id); id
----
(0 rows)
subquerytest=# select * from something where id in (select asdf);
ERROR: column "asdf" does not exist
LINE 1: select * from something where id in (select asdf); ^
subquerytest=#
--
I would imagine it has something to do with: "from something" means that "id" is available in the subquery and refers to the column in "something" and of course does not refer to "somethingelse" because that doesn't have such a column on it. And that I would have to disambiguate if it did by using aliases or table names preceding a dot.
If that is correct: I almost made a mistake in a subquery where I used the wrong column in the subquery. Is it possible to make the subquery refer only to values within its own specific from clause and error out otherwise? Maybe I could do that with a CTE?
Thanks.
