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

In article <JOEY.92Aug24091348_at_elysium.berkeley.edu> joey_at_berkeley.edu (Joe Hellerstein) writes:
>In article <1992Aug24.100941.24827_at_dk.oracle.com> bengsig_at_dk.oracle.com (Bjorn Engsig) writes:
>
>> Article <1992Aug23.074048.16681_at_prism.poly.edu> by sjha_at_prism.poly.edu
>> (Salil Kumar Jha) asks why this is illegal SQL:
>> |
>> |select * from emp
>> |where salary between
>> |(select salary from emp where ename = 'Larry')
>> |and
>> |(select salary from emp where ename = 'John')
>> |
>> The problem is that SQL is a poorly defined laguage. Read Date's
>> "Critique Of The SQL Language". There are numerous examples like yours.
>> --
>> Bjorn Engsig, Internet: bengsig_at_oracle.com
>> ORACLE Corporation. BANG-net: uunet!oracle!bengsig
>> Private : bjorn_at_login.dkuug.dk.
>
>Well *that's* not too helpful! No, SQL isn't great. But there's a
>better way of expressing the query, which is what the original poster
>was after.
>
>You want to avoid subqueries whenever possible. To get what you need,
>try the following:
>
>select distinct e1.*
> from emp e1, emp laremp, emp johnemp
> where laremp.ename = 'Larry'
> and johnemp.ename = 'John'
> and e1.salary between laremp.salary and johnemp.salary;
>
>(That's assuming I've got the "between" syntax right, which I'm not
>sure about.)
>
>This version of the query uses joins instead of subqueries, allowing
>for more join orders and join methods. The added Distinct is just
>there to avoid having lots of dups in the output. This might run
>marginally slower than your query (because of the sort for distinct),
>but is quite likely to run a whole lot faster (due to more join
>options for the optimizer.)
>
>If you want technical discussion of this issue, see
>"Extensible/Rule-Based Query Rewrite Optimization in Starburst" in
>SIGMOD '92.
>
>Joe Hellerstein

Actually, the reason the query won't work is that the sub-queries can return MULTIPLE values [employees named Larry] and the main query has to have ONE value for each end of the between.

Mike Received on Tue Aug 25 1992 - 22:58:11 CEST