On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote: > At 15:16 09/03/2016, you wrote: >> select id from a where id not in (select a.id from b); > > Shouldn't the engine bark on this, like it does on the modified version: > > select id from a where id not in (select zzz.id from b); > > "no such column zzz.id"
While there is no column named zzz.id, there is in fact a column named a.id. The query is syntactically valid. > As I understand it, there is no more an a.id column in table b. Yes, but why is that a problem? It is perfectly legal, and often useful, for a subquery to refer to columns from enclosing query. That's what makes it a *correlated* subquery. -- Igor Tandetnik
