Re: [GENERAL] why isn't this subquery wrong?

jonathan vanasco <postgres@2xlp.com> writes:
> Can anyone explain to me why the following is valid (running 9.6) ?

> SELECT foo_id
>     FROM example_a__data
>     WHERE foo_id IN (SELECT bar_id FROM example_a__rollup)
>     ;

Per the SQL standard, bar_id is interpreted as an "outer reference"
to example_a__data.bar_id.  This trips people up all the time, but
(a) it's required by spec and (b) there are cases where it's really
hard to do what you want without an outer reference.

Cautious SQL programmers qualify all references inside sub-selects
to avoid getting caught by this accidentally.

            regards, tom lane