| Author |
Topic |
|
owen912
Starting Member
31 Posts |
 Posted - 2004-08-26 : 13:20:46
|
| Normally when I write a join in a select statement I do something like this:SELECT ColumnNameA, ColumnNameBFROM TableA INNER JOINTableB ONTableA.TableId = TableB.TableIdThis is how I learned SQL. Recently I found myself working with a legacy database where all select statements using joins are written like this:SELECT ColumnNameA, ColumnNameBFROM TableA,TableB WHERE TableA.TableId = TableB.TableIdIs there a better/best way to join tables? Is one style “old and busted” and one “new highness?” Or is it one big fat “it depends?”Thanks,Mike |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 13:25:47
|
| practicalyl i think that inner join is preffered, because if you use 2nd way you might forget to do a link between table this resulting in cross joins.i also think (but i'm not sure) that its MS standard to use 1st instead of 2nd way.somebody will probably know more :)Go with the flow & have fun! Else fight the flow :) |
 |
|
|
owen912
Starting Member
31 Posts |
Posted - 2004-08-26 : 13:32:26
|
| I am not out to pick a fight, and will adhere to established standards.The database I am reviewing is older. So I was wondering if it's a matter of style, evolution of language, or if there is a preferred way in terms of performance etc. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 13:38:11
|
| who is fighting? :)))well i think that joins are here to do what they are meant to do - join tables on columns.where is used to filter the rowset returned. as simple as that :)Go with the flow & have fun! Else fight the flow :) |
|
|
|
owen912
Starting Member
31 Posts |
Posted - 2004-08-26 : 13:45:18
|
| "I'm a lover, not a fighter" :)))Thanks. That last bit about filtering the rowset clarified things quite a bit. I can see now how one is distinct from the other. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-26 : 13:53:43
|
| Use should strive for ANSInessWhich is what the first one is...the second one pre ANSI syntax...AND was the only way you could do it in the old days...Even up to Oracle 8i, that was THE only way to do it....Even with the advent of 6.5, people still used the old syntax...mostly because that's what they knew...You can still do it because it's backward compatible...See any of the old outer join syntax yet?Brett8-) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 13:56:30
|
| ANSI - that's the word i was looking for :)))) couldn't remeber it...Go with the flow & have fun! Else fight the flow :) |
|
|
|
owen912
Starting Member
31 Posts |
Posted - 2004-08-26 : 14:05:56
|
| I don’t see any of the old outer join syntax. I am embarrassed to say I don’t think I would recognize it when I see it. My SQL experience goes back to 1999, and being primarily self taught I don’t have a good grasp on what the old days are like. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-26 : 16:01:22
|
| Well, the old outer join syntax is *=And if you have any more doubts about which style you should use I provide the following quote from BOL:"The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax" - ( "*= operator" )/rockmoose |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2004-08-26 : 16:39:49
|
quote:
the second one pre ANSI syntax...
It's not. Both are ANSI SQL compliant. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-27 : 04:23:59
|
quote:
"The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax" - ( "*= operator" )
wait a minute!! as i understad this quote, we should change left and right join from table1 left join table2 on (...) totable1 *= table2 on (...) ??????are they serious???Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
Is there a better/best way to join tables?