Message-ID: <35e4de19cd13bf1b2744fccf647b94e0e4476b61.camel@cybertec.at>
Subject: Re: Subqueries
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Narsimham Chelluri <nchelluri@gmail.com>, pgsql-novice@postgresql.org
Date: Thu, 06 Mar 2025 15:23:27 +0100
In-Reply-To: 
 <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
References: 
	<CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/35e4de19cd13bf1b2744fccf647b94e0e4476b61.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-03-05 at 15:01 -0400, Narsimham Chelluri wrote:
> Can someone please explain to me why my first query does not return an er=
ror?=20
>=20
> subquerytest=3D# select * from something where id in (select id);
> =C2=A0id=20
> ----
> (0 rows)
>=20
> 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.

Right.

> And that I would have to disambiguate if it did by using
> aliases or table names preceding a dot.

Right again.

> 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 ref=
er
> only to values within its own specific from clause and error out otherwis=
e?
> Maybe I could do that with a CTE?

I follow the following rule:
Whenever an SQL statement refers to more than one table, qualify all column
references with the table alias:

  select * from something where something.id in (select somethingelse.id);

Yours,
Laurenz Albe