inner joins

[michaelnewport]

Greetings,

I like to write my inner joins as below,
but someone at work tells me its not as 'performant'
as using the 'inner join' statement.

Is this true ?
Is there a better way to write it ?

thanks
Mike

SELECT count(*)
FROM resources a, assignments b,
timesheets c, timesheetpayrollitems d
WHERE a.rsrchqnumber = 80002202
and a.rsrcguid = b.asgtrsrcguid
and b.asgtassignmentid = 0000006271
and b.asgtguid = c.tishasgtguid
and c.tishguid = d.tpittishguid
and d.tpitpayrollcode != 231
and d.tpitdaydate > '20050822'

[Simon Hayes]

The two joins are identical, and there is no performance difference -
MSSQL will handle them in exactly the same way. The INNER JOIN / OUTER
JOIN syntax is generally preferred for several reasons: some outer
joins can't be written in 'old style' joins; separating join conditions
from filter conditions is often more readable; Microsoft has said it
may remove support for 'old style' joins in a future version of MSSQL.

If you Google for "sql 2000 ansi joins", you'll find many more detailed
discussions.

Simon

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

The two joins are identical, and there is no performance difference -
MSSQL will handle them in exactly the same way. The INNER JOIN / OUTER
JOIN syntax is generally preferred for several reasons: some outer
joins can't be written in 'old style' joins; separating join conditions
from filter conditions is often more readable; Microsoft has said it
may remove support for 'old style' joins in a future version of MSSQL.

For old style *outer* joins that is.

The syntax that Mike used is part of the ANSI standard, and MS have
no plans to remove support for that syntax.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Simon Hayes]

Thanks - it's a good point to make that distinction.

Simon

[--CELKO--]

Actually, thre is a good story about introducing infixed join
operators. We needed to define a workable OUTER JOIN syntax, to
replace the various proprietary syntaxes that were in actual products.
Once that was defined, INNER JOIN, NATURAL JOIN, OUTER UNION and a
bunch of other options were easy to define. So we did. Committees are
like that. Get a copy of the SQL-92 specs and take a look.

I prefer the "traditional" inner join because it shows me all the
search conditions in an easy to read format. It lets me see n-ary
relationships like BETWEEN's.

There is also a rumor that the ON clauses have to hold the join
conditions and the WHERE clause holds the SARGs (Search Arguments).
Not true, but it lets you see what part of a SELECT can be extracted
into a VIEW.

I have a whoel discussionof this in SQL PROGRAMMNG STYLE.