Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sheetal
Starting Member
10 Posts |
 Posted - 2007-11-29 : 09:59:06
|
| I am new to SQL server. Please let me know which one is preferred out of two below and why. 1.SELECT pv.ProductID, v.VendorID, v.NameFROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v ON (pv.VendorID = v.VendorID)WHERE StandardPrice > $10 AND Name LIKE N'F%'2.SELECT pv.ProductID, v.VendorID, v.NameFROM Purchasing.ProductVendor pv, Purchasing.Vendor vWHERE pv.VendorID = v.VendorID AND StandardPrice > $10 AND Name LIKE N'F%'Both the queries fetch the same result. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-29 : 10:01:33
|
| 1st one is preferred since it is ANSI way of writing joins and hence portable across database products like SQL Server, Sybase, Oracle etc. Secondly it is more readable and more importantly it eliminates the chances of accidentally causing CROSS JOINS as quite common with NON-ANSI style of writing joins.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Sheetal
Starting Member
10 Posts |
Posted - 2007-11-29 : 11:01:38
|
| If there are 1 crore records in each table then query no 2 would result one cross join while the first query will select data according to the where clause. Am I correct sir? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-29 : 11:13:26
|
| No. I said that there is possibility of cross join if you miss the join condition in where clause which is not the case with 1st option since you have to specify conditions explicitly in the ON clause.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Sheetal
Starting Member
10 Posts |
Posted - 2007-11-29 : 11:30:32
|
| Thank you Harsh a ton :) |
|
|
|
|
|
|
Use of Join