public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Narsimham Chelluri <[email protected]>
To: [email protected]
Subject: Re: Subqueries
Date: Thu, 06 Mar 2025 15:23:27 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
References: <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>

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 error? 
> 
> subquerytest=# select * from something where id in (select id);
>  id 
> ----
> (0 rows)
> 
> 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 refer
> only to values within its own specific from clause and error out otherwise?
> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Subqueries
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox