Re: Is this a bug or a limitation of the SQL language

In article <BtLuFp.Kp6_at_watdragon.uwaterloo.ca> gnpaulle_at_maytag.uwaterloo.ca (Glenn Paulley) writes:

> > In article <BtJKIG.2K5_at_watdragon.uwaterloo.ca> gnpaulle_at_maytag.uwaterloo.ca (Glenn Paulley) writes:
> > > [I wrote:]
> > > |select * from emp
> > > |where salary between
> > > |(select salary from emp where ename = 'Larry')
> > > |and
> > > |(select salary from emp where ename = 'John')
 

> > > You want to avoid subqueries whenever possible. To get what you need,
> > > try the following:
> >
> However, the scalar subqueries above are not correlated, so
> I still fail to see how join processing could perform better than (basically)
> a single scan- nested loops aren't required. Again, I'm assuming that
> ename is indexed, or in some way it's possible to determine Larry
> and John's salary quickly.

You're right -- in this example, since the subqueries are scalar and not correlated, the expression above is as good as any other. My error. It's still a healthy knee-jerk reaction to convert subqueries to joins, but in this case it doesn't help.

Joe Hellerstein Received on Wed Aug 26 1992 - 23:27:13 CEST