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

Home -> Community -> Usenet -> comp.databases.theory -> Re: Simple SQL?

Re: Simple SQL?

From: Isaac Blank <izblank_at_yahoo.com>
Date: Sun, 20 May 2001 15:02:20 -0700
Message-ID: <u9XN6.91$At.114013@news.pacbell.net>

    The keyword is, of course, "multiple", right? Well, if there are multiple tables or table expressions mentioned in the FROM clause, then it is a join. In our case both FROM clauses you underlined only mention one table each, so they are not joins, but subqueries that happen to be correlated - because the WHERE clause contains references to the outer query context.

    We've been constantly told joins are superior to subqueries. This is not true. First, not every SELECT with subqueries can be converted to a join - and this particular thread is an excellent example of how ugly queries can get when people try to use joins inappropriately. Joins, on the other hand. can always be converted into an equivalent SELECT with subqueries with no mental effort at all - it's essentially a cut'n'paste job. Second, as optimizers get smarter and smarter, they should be able to produce same plans regardless of the form the particular query is written.

Isaac

"JRStern" <JRStern_at_gte.net> wrote in message news:3b0736ba.40625596_at_news.gte.net...
> On Fri, 18 May 2001 18:19:23 -0700, "Isaac Blank" <izblank_at_yahoo.com>
> wrote:
> >I would be glad to use ANSI syntax for joins - but there is no joins to
> >apply this syntax to. What you see here is a (relatively) simple SELECT
> >statement with correlated subqueries in the SELECT list. Similar to a
 join,
> >but not quite the same.
>
> There are multiple tables mentioned, there are joins going on.
>
> J.
>
> >> >SELECT a,
> >> > (SELECT SUM(b2) from Tblb WHERE Tbla.a=Tblb.b1),
>
> ^^^^^^^^^^^^^^^
>
> >> > (SELECT SUM(c2) from Tblc WHERE Tbla.a=Tblc.c1)
>
> ^^^^^^^^^^^^^^^^
>
> >> >FROM Tbla
> >>
> >> Doesn't anybody use ANSI join syntax??????
> >>
> >> J.
>
>
Received on Sun May 20 2001 - 17:02:20 CDT

Original text of this message

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