Re: Bug or expected behaviour

On Fri, 12 Dec 2003, Ravinder Bhalla wrote:

> billdb=# select * from emp where empid in ( select empid from dept);
> empid | empname | dept_no | age
> -------+---------+---------+-----
> 1 | Abhay | 100 | 24
> 2 | AB | 200 | 26
> (2 rows)
>
>
> Why the query is returning rows when empid column doesn't exists in dept
> table. Should throw an error.

No, it shouldn't. Basically, if a name can't be resolved in the
subselect, it will be tried as an outer reference (in this case to the
emp row being checked). Since there is an empid in the outer scope, it
resolves to that. IIRC, that's a spec mandated behavior, even if it's
often unwanted.