home *** CD-ROM | disk | FTP | other *** search
- Xref: sparky comp.databases:6318 comp.databases.theory:388 comp.databases.oracle:1373
- Path: sparky!uunet!cs.utexas.edu!uwm.edu!ogicse!sequent!muncher.sequent.com!map
- From: map@sequent.com (Michael Perry)
- Newsgroups: comp.databases,comp.databases.theory,comp.databases.oracle
- Subject: Re: Is this a bug or a limitation of the SQL language
- Message-ID: <1992Aug25.205811.13135@sequent.com>
- Date: 25 Aug 92 20:58:11 GMT
- Article-I.D.: sequent.1992Aug25.205811.13135
- References: <1992Aug23.074048.16681@prism.poly.edu> <1992Aug24.100941.24827@dk.oracle.com> <JOEY.92Aug24091348@elysium.berkeley.edu>
- Sender: usenet@sequent.com (usenet )
- Organization: Sequent Computer Systems Inc.
- Lines: 53
- Nntp-Posting-Host: sequent.sequent.com
-
- In article <JOEY.92Aug24091348@elysium.berkeley.edu> joey@berkeley.edu (Joe Hellerstein) writes:
- >In article <1992Aug24.100941.24827@dk.oracle.com> bengsig@dk.oracle.com (Bjorn Engsig) writes:
- >
- >> Article <1992Aug23.074048.16681@prism.poly.edu> by sjha@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@oracle.com
- >> ORACLE Corporation. BANG-net: uunet!oracle!bengsig
- >> Private : bjorn@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
-