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
