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

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