Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Is this a bug or a limitation of the SQL language

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

From: Darius S. Naqvi <darius_at_edm.isac.ca>
Date: Wed, 26 Aug 92 16:26:06 GMT
Message-ID: <1992Aug26.162606.1899@edm.isac.CA>

In article <1992Aug23.074048.16681_at_prism.poly.edu> sjha_at_prism.poly.edu (Salil Kumar Jha) writes:
>I tried to do this query in Oracle and DB2 and both failed. I know its
>not allowed but why not?
>
>Assume the use of the classic oracle emp table
>
>select * from emp
>where salary between
>(select salary from emp where ename = 'Larry')
>and
>(select salary from emp where ename = 'John')
>
>Isnt this the best way of finding all people whose salaries lie between those
>of Larry and John (without knowing whose is larger)
>
>sjha_at_prism.poly.edu
>

Even if the syntax were allowed, you would still have to know that John's salary were greater than or equal to Larry's, because

    where salary between x and y

is simply an abbreviation for

    where salary >= x and salary <= y

If x > y, the where clause will always fail, so you get no rows selected.

-- 
Darius S. Naqvi              mail at work: darius_at_edm.isac.ca
ISA Corp.                    mail at home: darius%naqvi.uucp_at_isac.ca
Edmonton, Alberta, Canada           phone: (403) 420-8081 
Received on Wed Aug 26 1992 - 11:26:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US