Re: Simple SQL?

    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 Mon May 21 2001 - 00:02:20 CEST