By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,099 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

inner joins

P: n/a
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'

Sep 7 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Sep 7 '05 #2

P: n/a
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

Sep 8 '05 #3

P: n/a
Thanks - it's a good point to make that distinction.

Simon

Sep 9 '05 #4

P: n/a
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.

Sep 10 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.