On Thu, Apr 20, 2017 at 3:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
I added an FAQ entry to the wiki for this question:
