Re: Is relational theory irrelevant? (was Re: Dreaming About Redesigning SQL)

"Lauri Pietarinen" <lauri.pietarinen_at_atbusiness.com> wrote in message news:3FB0A52C.2050105_at_atbusiness.com...
> Mikito Harakiri wrote:
> >select EX.EMP#
> >from EMP EX
> >where (select count(1)
> > from EMP EY
> > where EY.HEIGHT < EX.HEIGHT) < 5
> >
> >
> >
> Wow! That is BEAUTIFUL! And I bet it will be easy to teach an
> optimiser how to handle it best ;-)

I doubt your exclamation is unmistaken here, because all I did was copying and pasting from the first Date's "Quota Queries" article. Date also quotes equivalent self-join query, which is admittedly less intuitive. Query unnesting should be able to transform one form into another (the question might be if any product on the market can do that). Then, it is still a selfjoin, so that it have to be transformed into single table scan with stopcount.

> BTW, what if you had to order on more than one column (as is often
> the case)?

select EX.EMP#
from EMP EX
where (select count(1)

        from EMP EY
        where EY.HEIGHT < EX.HEIGHT or EY.HEIGHT = EX.HEIGHT  and EY.SAL <

or something more physical like this

select EX.EMP#
from EMP EX
where (select count(1)

        from EMP EY
        where padding(EY.HEIGHT)||padding(EY.SAL)  <