Posted by DA Morgan on 06/05/05 04:40

Erland Sommarskog wrote:

> 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 @busdatefrom AND @busdateto OR
> (@busdatefrom IS NULL AND @busdateto IS NULL))
> AND (con.accno = @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 @busdatefrom AND @busdateto OR
> (@busdatefrom IS NULL AND @busdateto IS NULL))
> AND (nte.accno = @accno OR @accno IS NULL)
>
> I know which one I rather have for breakfast.

And I'd likely agree. But then I wouldn't use this query as
a demo of EXPLAIN PLAN for what should be equally obvious reasons.

> 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.

Not true. Oracle has for multiple versions now allowed either ANSI or
ISO syntax.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

• Next in forum: Re: UPDATE/INSERT to make One-to-Many table become One-to-One
• Prev in forum: Re: UPDATE/INSERT to make One-to-Many table become One-to-One
• Thread view: Re: Code in the database or middle tier (the CLR controversy)

[Reply to this message]