Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

From: Connor McDonald <>
Date: Wed, 10 Jul 2002 21:44:33 +0100
Message-ID: <>

Paul Brewer wrote:
> Could anyone enlighten me please?
> I ran into this behaviour today, and a search on metalink indicated
> that apparently it is expected.
> BTW: on HPUX (and I understand many other versions).
> select count(*) from emp where empno in (select empno from dept);
> /* Of course, DEPT has no EMPNO column */
> I think this should raise an exception, but instead it returns
> a count of the rows in EMP, apparently because although EMPNO is not
> a column in the DEPT table, it is within scope for the query,
> since a column with this name exists in the EMP table,
> thus we are effectively saying 'where EMP.EMPNO = EMP.EMPNO'
> I find it difficult to buy this, since however Oracle is going to
> merge the subquery or optimise the plan, as far as I can see,
> conceptually it should do this:
> 1) Evaluate the result of the subquery, returning one of:
> an exception
> no rows
> one or more rows
> 2) Pass this result to the outer query as a list of values for the
> predicate.
> In other words, IMHO the subquery should behave as if it were
> self-contained, without reference to the outer query.
> Since the subquery is not in and of itself valid,
> the whole thing should fail.
> There may well be issues of which I am unaware, and I am sorry
> if this is an old one, but I would appreciate input on this,
> since what Oracle appear to be saying (on metalink) appears to me
> to be totally counter-intuitive.
> Thanks in advance,
> Paul

Its easy enough to see where this would cause confusion, but the construct (ie searching 'parent' tables for column names) is required for correlated subqueries eg

select * from tableA
where col in ( select ... from tableB where tableA.other_col ... )

The moral is - always associate a table alias in queries that use more than one table. I haven't tested it but I would not be surprised if doing improves the parse time as well.


Connor McDonald

"Some days you're the pigeon, some days you're the statue..."
Received on Wed Jul 10 2002 - 15:44:33 CDT

Original text of this message