Posted by Erland Sommarskog on 06/05/05 01:35

DA Morgan (damorgan@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

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.

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

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.

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

[Back to original message]