Re: [SQL] how to avoid an inner join

"Thomas Schwickert" <schwickert_at_yahoo.com> wrote in message news:fc1cd77860385d8a80ba4c5f873dc71e.20305_at_mygate.mailgate.org...
> Hi
>
> "Harald Fischer-Hohlweck" <Harald.Fischer-Hohlweck_at_ibix.de> wrote in
message
> news:9o4urg$2c1n$1_at_news.lf.net...
>
> > Hi.
> >
> > Assume a table having a key Key and a column ValidFrom (ValidFrom is a
> > date). All rows with a definite Key have different ValidFrom's. Can I
> > get the row with the youngest ValidFrom in the past (i.e. the row that
> > is valid actually) without an inner join? Something like
> > select * from table
> > where Key = value and ValidFrom = "max(ValidFrom) <= today";
> >
> > I'm no database expert, but I anticipate performance problems if I have
> > to do the inner join.
> >
>
> select key,datum from xxx o
> where key=1
> and datum =
> (select max(datum)
> from xxx i
> where i.key=o.key)
>
> there should be indexes on k, perhaps on (k,datum)
>
> hth
> Thomas

You did not avoid a inner join?
A subquery like this, in essence is going to be converted to an inner join in oracle.
The only way to prevent an inner join in this case is to do this procedurally!

declare
cursor x is select key,datum from xxx order by datum;

........
........
........

Anurag Received on Mon Sep 17 2001 - 10:49:48 CDT