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

Hey Galen, don't go and doubt me now, we're almost there buddy !!

This is not a good example as it makes no sense to write it as it has been.

But if you agree that the var column in the subquery represents the var in T1 in the outer query, which occurrence of var are we talking about. The answer is *the current one* as being processed in the outer query.

Therefore we must execute the subquery checking for the current value of var for as many rows as we process in the outer query. A correlated subquery !! If there were 10 rows in T1, the subquery would be executed 10 times. The execution plans and stats would confirm this.

I noticed a posting by Tom Kyte in this tread. He points to a very similar example in his link.

Regards

Richard
"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:uit3nd89w.fsf_at_grossprofit.com...
> On Wed, 10 Jul 2002, richard.foote_at_bigpond.com wrote:
>
> > The inner query is hence referencing a column from the outer
> > query to create an equivalent predicate to 'where t1.id in
> > (select t1.var from t2)'; (which is always false with this lack
> > of data).
> >
> > Therefore the subquery is referencing the outer query therefore
> > this is an example of a correlated subquery ?
> >
> > I'm beginning to get confused now :)
>
> Me to. I can see how the query,
>
> select count(*) from t1 where id in (select var from t2);
>
> could be considered a subquery, but var doesn't "join" to
> anything in t2, so what is it correlating on?
>
> --
> Galen Boyer
Received on Wed Jul 10 2002 - 22:34:26 CDT