March 27, 2014 at 10:03 am
When performing an inner join is it better to do this
--Query 1
SELECT sd.CarrierTrackingNumber,
sd.LineTotal,
sh.CustomerID,
sh.DueDate
FROMAdventureWorks2012.Sales.SalesOrderDetail sd
INNER JOIN
AdventureWorks2012.Sales.SalesOrderHeader sh ON
sd.SalesOrderID = sd.SalesOrderID
Or do this
--Query 2
SELECT sd.CarrierTrackingNumber,
sd.LineTotal,
sh.CustomerID,
sh.DueDate
FROMAdventureWorks2012.Sales.SalesOrderDetail sd,
AdventureWorks2012.Sales.SalesOrderHeader sh
WHEREsd.SalesOrderID = sd.SalesOrderID
March 27, 2014 at 10:13 am
The first one is ANSI compliant and so is usually preferred. I prefer it also because it keeps the join predicate separate from the WHERE clause. Finally, for outer joins, the old-style syntax (*=, =*) is no longer supported, so for consistency it is better to use the first form.
John
March 27, 2014 at 10:20 am
Thank You
March 27, 2014 at 10:28 am
To be clear, both are ANSI compliant, just different versions. The first is the SQL-92 and the second one is SQL-86.
As a best practice I would suggest that you follow the SQL-92 standard (just as John did).
March 27, 2014 at 10:29 am
John Mitchell-245523 (3/27/2014)
The first one is ANSI compliant and so is usually preferred. I prefer it also because it keeps the join predicate separate from the WHERE clause.
Actually both of them are ANSI compliant. One is ANSI-89 and the other is ANSI-92.
I totally agree that keep the join logic separate is better. It is pretty easy to get an accidental cross join if you miss a condition in the join condition and this likelihood is increased because it is more difficult to read.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 10:31 am
Seems that Luis was posting as I was typing. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 10:33 am
I do agree the first one is easyer to read. I was pulling some old code and ran into the second way of doing it and I could not remember which one was more efficent.
March 27, 2014 at 10:47 am
David Stout (3/27/2014)
I do agree the first one is easyer to read. I was pulling some old code and ran into the second way of doing it and I could not remember which one was more efficent.
Both will perform the same way. They're both interpreted the same way by the engine, the difference remains only on the user interface.
April 3, 2014 at 6:48 am
I would go with the first one.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply