This is a discussion on Outer Join Operator Gives Different Results Than Sql-96 Outer Join within the SQL Server forums, part of the Database category; I'm converting Compatibility Level 80 SQL code to Compability Level 90, to upgrade from SQL Server 2000 to SQL ...
|
|||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
Outer Join Operator Gives Different Results Than Sql-96 Outer Join
I'm converting Compatibility Level 80 SQL code to Compability Level 90, to upgrade from SQL Server 2000 to SQL Server 2005.
I have hit a stumbling block when converting statements with OUTER JOIN operators *= and =* to LEFT OUTER JOIN and RIGHT OUTER JOIN statements, respectively. Here is a comparison of results from the old and new syntax: SELECT f.hz, f.amp FROM freq f (NOLOCK), outcome o (NOLOCK), flip fl1 (NOLOCK) WHERE o.id =* f.id AND fl1.od =* f.od -- returns 479,756 rows SELECT f.hz, f.amp FROM freq f (NOLOCK) right outer join outcome o (NOLOCK) on o.id = f.id right outer join flip fl1 (NOLOCK) on fl1.od = f.od -- returns 415,533 rows Can anyone help me figure out why the results are not the same, and how to formulate the new version query in order to obtain the same results as the old version? Thanks for your help! RandyB |