Re: Code in the database or middle tier (the CLR controversy)

DA Morgan (damorgan_at_psoug.org) writes:
> Consider these two INNER JOINS formatted to match.
>
> -- ISO syntax
> SELECT p.last_name, t.title_name
> FROM person p , title t
> WHERE p.title_1 = t.title_abbrev;
>
> -- ANSI syntax
> SELECT p.last_name, t.title_name
> FROM person p INNER JOIN title t
> ON p.title_1 = t.title_abbrev;
>
> Line 2:
> What advantage in replacing a single
> comma with "INNER JOIN"?
>
> Line 3:
> What value in replace WHERE with ON

[Quoted] In this example: not very much. But digest this:

   FROM notes nte, insloannotes iln, contnotes con, instruments ins,

        trades trd, accounts acc, customers cst, addresses adr,
        customeraddresses cad, insloanstartnotes ils, countries cou,
        notprintedcontnotes npc
   WHERE nte.nteid           = npc.conid
   AND   npc.printdate       IS NULL
   AND   nte.nteid           = iln.conid
   AND   iln.loanconid       = con.conid
   AND   nte.trdid           = trd.trdid
   AND   con.insid           = ins.insid
   AND   iln.loanevent       = 'I'
   AND   iln.loanconid       = ils.conid
   AND   nte.accno           = acc.accno
   AND   acc.cstno           = cad.cstno
   AND   acc.cstno           = cst.cstno
   AND   cad.isdefaultsendout= 1
   AND   cad.adrid           = adr.adrid
   AND   adr.coucode         = cou.coucode
   AND   trd.butcode         = 'IIL'
   AND  (trd.tradedate       BETWEEN _at_busdatefrom AND @busdateto OR
                             (_at_busdatefrom IS NULL AND @busdateto IS NULL))
   AND  (con.accno           = _at_accno OR @accno IS NULL)

versus:

   FROM notprintedcontnotes npc

   JOIN   notes nte               ON nte.nteid = npc.conid
   JOIN   trades trd              ON trd.trdid = nte.trdid
   JOIN   insloannotes iln        ON nte.nteid = iln.conid
   JOIN   contnotes con           ON con.conid = iln.loanconid
   JOIN   insloanstartnotes ils   ON con.conid = ils.conid
                                 AND iln.loanconid = ils.conid
   JOIN   instruments ins         ON con.insid = ins.insid
                                 AND trd.insid = ins.insid
   JOIN   accounts acc            ON nte.accno = acc.accno
                                 AND con.accno = acc.accno
   JOIN   customers cst           ON acc.cstno = cst.cstno
   JOIN   customeraddresses cad   ON acc.cstno = cad.cstno
                                 AND cad.isdefaultsendout = 1
   JOIN   addresses adr           ON adr.adrid = cad.adrid
   LEFT   JOIN countries cou      ON adr.coucode = cou.coucode
   WHERE  npc.printdate IS NULL
     AND  iln.loanevent = 'I'
     AND  trd.butcode   = 'IIL'
     AND  (trd.tradedate BETWEEN _at_busdatefrom AND @busdateto OR
          (_at_busdatefrom IS NULL AND @busdateto IS NULL))
     AND  (nte.accno  = _at_accno OR @accno IS NULL)

I know which one I rather have for breakfast.

> If performance is identical? The issue is not much
> different from asking which is better, a car with
> the steering on the right or a car with the steering
> on the left.

On SQL Server, the performance is indeed identical. The story is the query processor rewrites the query internally. Obviously, I cannot tell what Oracle does.

> Now I know those used to the ANSI syntax will likely
> be thinking compatibility with other systems. But
> those with 10+ years in Oracle will be thinking: So
> what!

[Quoted] Actually, as long as we are into inner joins, both syntaxes are ANSI-compatible. It is when it comes to outer joins it matter. Here both SQL Server and Oracle have their own propritary operators. I don't know about the Oracle operator, but the *= in SQL Server is very problematic. For this reason, there is a strong recommendation to use ANSI JOIN for outer joins, and once you are there, it's logical to use it for inner joins as well. But this is for SQL Server. If the Oracle outer-join operator (+= is it?) is sounder, you certainly have one incentive less to change.

--
[Quoted] Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp