Outer Joins - ANSI vs Oracle Join (+) Syntax

There are many problems or rather restrictions to use oracle specific join (+) operator while writing SQLs. In fact, there are few places where (+) syntax can't be used.
Let's see them with examples.

Oracle itself recommends that you use the FROM clause OUTER JOIN (ANSI) syntax rather than the Oracle join operator.

Did you know? 
Oracle developed it's (+) join syntax before the ANSI committee had completed the definitions for the joins. That's why Oracle is still supporting (+) join operator for backwards compatibility reasons.

Oracle Specific Syntax:

    SELECT EMP.name, DEP.name 
    FROM employee EMP, department DEP 
    WHERE EMP.dep_id = DEP.dep_id (+);

ANSI Syntax:

    SELECT EMP.name, DEP.name     
    FROM employee EMP          
       LEFT OUTER JOIN department DEP 
        ON EMP.dep_id = DEP.dep_id;

1) ANSI standard join notation comes with the advantage that it is not vendor specific and will be same in any RDBMS (like Microsoft Sql Server, MySql etc.). So you don't need to touch the logic, if you are porting your database to non-oracle RDBMS. 
Or let me put this way. A non-oracle database developer can also understand your code, if it written using ANSI syntax. So, you have wider audience who can understand and maintain your code. ANSI syntax is more readable too.
   
2) You can't do FULL OUTER JOIN on two tables using oracle (+) syntax. 
    You have to do it manually with a UNION ALL of two joins.
   
 Example:
Assume 3 tables. A is related to B and A is also related to C.

Wrong Query:

         SELECT A.*, B.*
         FROM tableA A, tableB B
         WHERE A.col1 (+) = B.col1 (+);

        ORA-01468: a predicate may reference only one outer-joined table

Correct Query:

         SELECT A.*, B.* FROM tableA A, tableB B WHERE A.col1 (+) = B.col1 UNION ALL SELECT A.*, B.* FROM tableA A, tableB B WHERE A.col1 = B.col1 (+);

ANSI Syntax:

SELECT A.*, B.*
FROM tableA A 
             FULL OUTER JOIN tableB B
ON A.col1 = B.col1;

3) Using (+) syntax, you can't OUTER JOIN a table to two or more tables.
   You have to manually create a subquery to achieve this

       Example:
Assume 3 tables. A is related to B and B is related to C.

Wrong Query

  SELECT A.*, B.*, C.*
FROM tableA A, tableB B, tableC C
WHERE B.col1  = A.col1 (+)
       AND  C.col2 = A.col2 (+) 

ORA-01417: a table may be outer joined to at most one other table

Correct Query

SELECT C.*
FROM
       (
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
       ) TEMP, C
WHERE  C.col2 = TEMP.col2 (+) 

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
         RIGHT OUTER JOIN tableB B
           ON B.col1 = A.col1
         RIGHT OUTER JOIN tableC C
            ON C.col2 = A.col2

4) You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.

   Wrong Usage
SELECT temp.*, C.*
FROM (tableA A  JOIN tableB B ON B.col1 = A.col1) temp
            , C   
         WHERE  C.col2 = temp.col2

5) The (+) operator can appear only in the WHERE clause or, 
   in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.  

6) If A and B are joined by multiple join conditions, 
   then you must use the (+) operator in all of these conditions. 
   If you do not, then Oracle Database will return only the rows resulting 
   from a simple join, but without a warning or error to advise you that 
   you do not have the results of an outer join.

          Wrong Usage (Though syntactically correct)
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
AND B.col2    = A.col2
AND B.col3    = A.col3

   Correct Usage 

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
AND B.col2    = A.col2 (+)
AND B.col3    = A.col3 (+)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
RIGHT OUTER JOIN tableB B
ON B.col1  = A.col1
AND B.col2    = A.col2
AND B.col3    = A.col3

7) The (+) operator does not produce an outer join, if you specify one table in 
   the outer query and the other table in an inner query.

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1
AND A.col2 IN (SELECT C.col2 
                       FROM tableC 
                       WHERE C.col2 = A.col2(+) 
                       )
ORA-01705: an outer join cannot be specified on a correlation column

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1
AND A.col2 IN (SELECT C.col2 
                       FROM tableC 
                       WHERE C.col2 = A.col2
                       )

8) You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.

Wrong Query

SELECT * 
FROM tableA
WHERE A.col1 = A.col2 (+)
ORA-01416: two tables cannot be outer-joined to each other

Right Queries

SELECT * 
FROM tableA
WHERE A.col1 = A.col2;
(Inner joins are valid on the same table)
--
SELECT * 
        FROM tableA A1, tableA A2
WHERE A1.col1 = A2.col2 (+)

ANSI Syntax (Mandates for self join)

SELECT * 
FROM tableA A1 
LEFT OUTER JOIN tableA A2
   ON A1.col1 = A2.col2

9) The (+) operator can be applied only to a column, not to an arbitrary expression. 
   However, an arbitrary expression can contain one or more columns marked with the (+) operator.  

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = (B.col1 * 123 )(+)
ORA-00936: missing expression
--
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 * B.col2(+)
ORA-01417: a table may be outer joined to at most one other table

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) * 123
--
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1(+) * B.col2(+)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
           LEFT OUTER JOIN tableB B
ON A.col1 = B.col1 * 123

10) A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE (A.col1 = B.col1 (+) 
              OR
           A.col2 = B.col2)

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE (A.col1 = B.col1 
            OR
          A.col2 = B.col2)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A
RIGHT OUTER JOIN tableB B
ON ( A.col1 =  B.col1
OR 
                A.col2 = B.col2)

11) A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
Similar to the above point.

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) 
       AND A.col1(+) IN (1,2,3)

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Right Query (But this is no more an outer join)

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) 
       AND A.col1 IN (1,2,3)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A
RIGHT OUTER JOIN tableB B
ON A.col1 =  B.col1
AND A.col1 IN (1,2,3)

Reference
Oracle® Database SQL Language Reference (11g Release 1)