MIME-Version: 1.0
From: Narsimham Chelluri <nchelluri@gmail.com>
Date: Wed, 5 Mar 2025 15:01:49 -0400
Message-ID: 
 <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
Subject: Subqueries
To: pgsql-novice@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a838b3062f9d071a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE%3D-mSAKkyL5g%40mail.gmail.com>
Precedence: bulk

--000000000000a838b3062f9d071a
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a838b3062f9d071a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div><br></div><div>Can someone please ex=
plain to me why my first query does not return an error? <br></div><div><br=
></div><div>--</div><div>
<pre class=3D"gmail-c-mrkdwn__pre">subquerytest=3D# create table something =
(id bigint generated always as identity primary key);
CREATE TABLE
subquerytest=3D# create table somethingelse (asdf text);
CREATE TABLE
subquerytest=3D# select * from something where id in (select id from someth=
ingelse);
 id=20
----
(0 rows)

subquerytest=3D# select id from somethingelse;
ERROR:  column &quot;id&quot; does not exist
LINE 1: select id from somethingelse;
               ^
subquerytest=3D# select * from something where id in (select id);
 id=20
----
(0 rows)

subquerytest=3D# select * from something where id in (select asdf);
ERROR:  column &quot;asdf&quot; does not exist
LINE 1: select * from something where id in (select asdf);
                                                    ^
subquerytest=3D# </pre>

</div><div>--</div><div><br></div><div>I would imagine it has something to =
do with: &quot;from something&quot; means that &quot;id&quot; is available =
in the subquery and refers to the column in &quot;something&quot; and of co=
urse does not refer to &quot;somethingelse&quot; because that doesn&#39;t h=
ave such a column on it. And that I would have to disambiguate if it did by=
 using aliases or table names preceding a dot.</div><div><br></div><div>If =
that is correct: I almost made a mistake in a subquery where I used the wro=
ng column in the subquery. Is it possible to make the subquery refer only t=
o values within its own specific from clause and error out otherwise? Maybe=
 I could do that with a CTE?</div><div><br></div><div>Thanks. <br></div><di=
v><br></div><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=
=3D"gmail_signature"><div dir=3D"ltr">- Narsa</div></div></div></div>

--000000000000a838b3062f9d071a--