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

Hi Paul,

I know where you are coming from with this and at first glance it does look kind of odd but Daniel is perfectly correct in what he says.

You need to break it up into the two queries and consider how correlated subqueries work. Remember, in a correlated subquery you are referencing a column in the inner query that is based on a column in the outer query. The inner query must be able to access columns in the outer query else correlated subqueries would not be possible (which would be highly unfortunate).

Therefore based on the above, 'select empno from dept' *has* to be interpreted as (impression of Oracle coming up), "Well as there is no empno in dept, you *must* mean the empno in the outer query, that being emp. Therefore you want to select the empno that corresponds to the current empno in the outer query". That of course is always going to be true.

We have two choices.

1. write to Oracle Corporation to disable the use of correlated subqueries, or
2. fix the code (select count(*) from emp where deptno in (select deptno from dept) or some such. (hint hint).

I've seen my fair share of SQL where my first reaction is "What the ...". It does eventually make sense (most of the time :)

Good Luck

Richard

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D2B6435.9AFCFD2E_at_exesolutions.com...
> Paul Brewer wrote:
>
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3D2B4AEA.EF7D9D1_at_exesolutions.com...
> > > And your concept of what a correlated sub-query should do is ...?
> > >
> > I didn't find this post particularly helpful.
> > Still looking for suggestions.
> >
> > Thanks,
> > Paul
>
> You weren't asking for help. You were wishing Oracle would change a very
> standard SQL behavior that has, as far as I know, not caused any
> problems for at least a million other developers.
>
> I was trying to point out that were your suggestion to be taken it would
> require Oracle to break correlated subquerys. I am sorry if you didn't
> catch that. The query did exactly what you asked it to do. Nothing more.
> Nothing less.
>
> Daniel Morgan
>
Received on Tue Jul 09 2002 - 20:10:50 CDT