Subqueries

Поиск
Список
Период
Сортировка
От Narsimham Chelluri
Тема Subqueries
Дата
Msg-id CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com
обсуждение исходный текст
Ответы Re: Subqueries
Re: Subqueries
Список pgsql-novice
Дерево обсуждения
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.

- Narsa

В списке pgsql-novice по дате отправления: