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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2002-02-19 : 20:37:04
|
| I know that using "LEFT OUTER JOIN" syntax is preferred to "*=" but can someone explain why the first statement works while the second gives an error. Aren't the two statements the same? ----------- GOOD --------------select * FROM(Table1 a LEFT OUTER JOIN Table2 b ON a.key1 = b.key1) INNERJOIN Table3 c ON b.key2 = c.key2---------- BAD ----------------SELECT * FROMTable1 a, Table2 b, Table3 cWHERE a.key1 *= b.key1 ANDb.key2 = c.key2 error msg: The table 'Table2' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.Nic |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-19 : 21:49:57
|
| I think the error message says it all: the *= join syntax cannot support mixed JOIN types. You would have to make all of these joins the same. That's another reason not to use it.There are some subtle differences that affect how the two types of join are processed, and there are circumstances where they are NOT equivalent. Going forward, do not use the *= syntax anymore, and you should make an effort to convert any existing code like that to the LEFT/RIGHT JOIN syntax. Ultimately it will stop working altogether and you'll have to make this conversion anyway. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-20 : 03:08:31
|
| It's becasue the left outer join syntax supports this whereas the abbreviated syntax doesn't (nothing to do with mixing join types).It's because microsoft didn't bother to change the syntax checker for the abreviated syntax and a new feature that became available with the ansii syntax.It's also an example of how a view can give an error whereas a table doesn't if you use the abbreviated syntax.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
*= vs LEFT OUTER JOIN