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 |
|
tosscrosby
Aged Yak Warrior
676 Posts |
 Posted - 2009-03-13 : 10:02:51
|
| I've been seeing something similar to #1 as a suggested solution quite a bit lately. However, I most often create code using #2. Both return the same result sets and the execution plans are very similar in tasks - cost % are pretty consistent between the 2 as well. I guess my point is - is one more accepted/common than the other these days? As I only have SQL 2000 servers (but that's about to change - finally!), is #1 more common within SQL 2005 and/or 2008? My opinion is #2 is cleaner but I also want to make sure I'm keeping up with new standards/practices, if this indeed one. Any and all thoughts would be most appreciated.1) Select f.SSN, i.UserName From tblUser AS f JOIN (SELECT UserName, SSN FROM tbluserProfileinfo) AS i ON i.SSN = f.SSN2) Select f.SSN, i.UserName From tblUser AS f join tbluserProfileinfo i ON i.SSN = f.SSNTerry-- Procrastinate now! |
|
|
souLTower
Starting Member
39 Posts |
Posted - 2009-03-13 : 14:05:50
|
Both are valid. #1 is not necessary for this query. It uses a derived query. A great place for a derived query is when the data you want to join to is not as straightforward as in this example. Here's an example of a place to use a derived query. Say you have a table or widgets with details etc and you want a list of the most recent item, you need all of the details. select W.name, W.itemNumber, W.someOtherField from widgets W inner join( -- This derived query returns the most recent item number by name select max(itemNumber) as itm from widgets group by name) D ON W.itemNumber = D.itm Either way is valid. It's best to use what works best for your needs, what performs best, and what will be most maintainable.God Bless |
 |
|
|
|
|
|
Pros/cons/comments requested - por favor