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

Home -> Community -> Usenet -> c.d.o.misc -> Re: subselect SQL*plus

Re: subselect SQL*plus

From: tzadkiel <tzadkiel_at_surfnetcity.com.au>
Date: 1998/03/09
Message-ID: <01bd4af7$7b56b720$85ce6ccb@default>#1/1

Tony <tonys_at_lords.com> wrote in article <35033826.4F94_at_lords.com>...
> tzadkiel wrote:
> >
> > The answer they are probably looking for is:
> >
> > select flight, time_of_departure, time_of_arrival
> > from table1
> > where departing_from_code = 'CDG'
> > and arrives_at_code in (select code
> > from citycode
> > where city = 'LONDON')
>
> This is only a note to tzadkiel. Without knowing anything about the
> original problem I would in the real life write:
>
> select flight, time_of_departure, time_of_arrival
> from table1
> where departing_from_code = 'CDG'
> and exists (select code
> from citycode
> where code = arrives_at_code and city =
> 'LONDON')
>

or you could save the parser the trouble of restructuring the query as a join and just write

select a.flight, a.time_of_departure, a.time_of_arrival from table1 a, citycode c

where a.departing_from_code = 'CDG'
  and a.arrives_at_code = c.code
  and c.code = 'LONDON'

a dozen different way to get from here to there. (and yes, "exists" is going to be faster than "in" in most cases. Received on Mon Mar 09 1998 - 00:00:00 CST

Original text of this message

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