Re: Subqueries

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