Outer Join Operator (+) - Oracle to SQL Server Migration

Oracle outer join operator (+) allows you to perform outer joins on two or more tables.

Quick Example:

   -- Select all rows from cities table even if there is no matching row in counties table
   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id = countries.id(+);

Summary information:

Using	In the WHERE clause
Multiple Join Columns		(+) must be applied to all columns
Alternatives	LEFT OUTER JOIN	Used in FROM clause	ANSI SQL Compatible
	RIGHT OUTER JOIN

Last Update: Oracle 11g Release 2

Assume that you have the following table definitions and data.

Oracle:

   -- Define tables 
   CREATE TABLE countries 
   (
       id NUMBER(3), 
       name VARCHAR2(70)
   );
 
   CREATE TABLE cities 
   ( 
       name VARCHAR2(70), 
       country_id NUMBER(3)
   );
 
   -- Data
   INSERT INTO countries VALUES (1, 'France');
 
   INSERT INTO cities VALUES ('Paris', 1);
   INSERT INTO cities VALUES ('London', 2);
   COMMIT;

There are 2 rows in the cities table, but if you use inner join with the counties table, only one row is selected.

Oracle:

   -- Using inner join
   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id = countries.id;

Output:

Using the outer join operator you can retrieve all cities no matter whether there is a matching row in the countries table or not.

Oracle:

   -- Using outer join operator
   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id = countries.id(+);

Output:

You can convert Oracle outer join operator to ANSI SQL LEFT OUTER JOIN or RIGHT OUTER JOIN in Microsoft SQL Server or SQL Azure.

SQL Server:

   -- Using ANSI SQL outer join syntax
   SELECT cities.name, countries.name 
   FROM cities LEFT OUTER JOIN countries ON cities.country_id = countries.id;

Output:

The previous versions of SQL Server also provided outer join operator *= that available up to SQL Server 2008 R2.

By default it is disabled in SQL Server 2008 R2.

SQL Server 2008 R2:

   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id *= countries.id;
   -- Error:
   -- Msg 4147, Level 15, State 1, Line 3
   -- 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

But you can enable outer join operator *= using SET COMPATIBILITY_LEVEL option in the ALTER DATABASE statement.

SQL Server 2008 R2:

   -- Enable outer join operator *=
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 80;
 
   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id *= countries.id;

Output:

But there is no way to enable the outer join operator *= in SQL Server 2012.

SQL Server 2012:

   -- Try to enable outer join operator *=
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 80;
   -- Error:
   -- Msg 15048, Level 16, State 1, Line 1
   -- Valid values of the database compatibility level are 90, 100, or 110.
 
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 90;
 
   SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id *= countries.id;
   -- Error:
   -- Msg 102, Level 15, State 1, Line 3
   -- Incorrect syntax near '*='.

• Oracle to SQL Server Migration

Oracle 11g R2 SQL Language Reference

• Joins

Microsoft SQL Server 2012 - Books Online

• FROM Clause
• ALTER DATABASE Compatibility Level

Dmitry Tolpeko, dmtolpeko@sqlines.com - May 2012