Oracle轉換至SQL Server時，Outer Join運算子的處理方式

最近接到從Oracle轉移到SQL Server的案件，其中由於Oracle在使用查詢中使用了特定的Outer-Join Operators(*=、=*)語法(Non-ANSI)，所以在轉換到SQL Server上就遇到問題了，其實在SQL Server 2000的版本上是有支援這個語法的，但是在後續的版本上就會遇到下列的問題。

執行語法：
SELECT e1.[EmployeeID]
      ,e1.[EmployeeName]
      ,e1.[Title]
      ,e1.[ManagerID]
      ,e2.[EmployeeName] mag_name
  FROM [Northwind].[dbo].[Employees] e1, [Northwind].[dbo].[Employees] e2
  where e1.[ManagerID] *= e2.[EmployeeID]

錯誤訊息：
Msg 4147, Level 15, State 1, Line 7
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification,
please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

解決方法其實很簡單，就是將資料庫的相容性層級(Compatibility Level)設定成80，如此就可以直接使用，但由於在SQL Server 2012已不支援將相容性層級設定成80，所以強烈建議儘量改寫這部份的語法(如下列)，以免日後升級時會遇到同樣的問題。

建議語法： SELECT e1.[EmployeeID]
      ,e1.[EmployeeName]
      ,e1.[Title]
      ,e1.[ManagerID]
      ,e2.[EmployeeName] mag_name
  FROM [Northwind].[dbo].[Employees] e1 Left Outer Join [Northwind].[dbo].[Employees] e2
  On e1.[ManagerID] = e2.[EmployeeID]

在SQL Server 2012上，如果要嘗試將相容性層級變更成80時，會遇到下列的錯誤訊息，因為已不支援這個相容性層級了。

錯誤訊息：
Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 90, 100, or 110.

關鍵字：SQL Server、Compatibility Level、Outer Join Operators