ORACLE 9i now supports the new outer joins and natural join syntax, which is a great relief. The old outer join operator is just awkward and difficut to read, not to mention with a lot of restrictions attached. ORACLE now of course recommends using the new ANSI/ISO SQL:1999 compliant syntax.


SQL> select * from t1;

        C1 C2
---------- ----------
         1 1
         2 2
         3 3

SQL> select * from t2;

        C1 C2
---------- ----------
         2 a
         3 b
         4 c

-- The old (+) operator
SQL> select * from t1, t2 where
  2  t1.c1=t2.c1(+);

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         1 1
         2 2                   2 a
         3 3                   3 b

-- And now...
SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1);

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         2 2                   2 a
         3 3                   3 b
         1 1

SQL> 

-- t2.c2='a' is called post-join predicate
-- Get me only rows from the outer join that has t2.c2='a'
SQL> select * from t1, t2 where
  2  t1.c1=t2.c1(+)
  3  and t2.c2='a';

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         2 2                   2 a

SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1)
  2  where t2.c2='a';

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         2 2                   2 a

SQL> 

-- 'a'=t2.c2(+) is called pre-join predicate
-- Give me all row from t1, give me only rows from t2 that satisfy both 
-- predicates.
SQL> select * from t1, t2 where
  2  t1.c1=t2.c1(+) and 'a'=t2.c2(+);

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         1 1
         2 2                   2 a
         3 3

SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1) and t2.c2='a';

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         2 2                   2 a
         3 3
         1 1

SQL> 


How do you get these 2 rows?

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         1 1
         2 2                   2 a

-- Get the out join set first and then apply
-- additional conditions.
SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1)
  2  where t2.c2 is null or t2.c2='a';

        C1 C2                 C1 C2
---------- ---------- ---------- ----------
         2 2                   2 a
         1 1

SQL> 


-- Outer join involves more than 2 tables 
-- Get the result from the first outer join and then join it to the 3rd table
SQL> select * from t1, t2, t3 where
  2  t1.c1=t2.c1(+) and t2.c2=t3.c2(+);

        C1 C2                 C1 C2                 C1 C2
---------- ---------- ---------- ---------- ---------- ----------
         2 2                   2 a                   1 a
         3 3                   3 b
         1 1

SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1)
  2                   left outer join t3 on (t2.c2=t3.c2);

        C1 C2                 C1 C2                 C1 C2
---------- ---------- ---------- ---------- ---------- ----------
         2 2                   2 a                   1 a
         1 1
         3 3                   3 b

-- Similarly
SQL> select * from t1, t2, t3 where
  2  t1.c1=t2.c1(+) and t1.c1=t3.c1(+);

        C1 C2                 C1 C2                 C1 C2
---------- ---------- ---------- ---------- ---------- ----------
         1 1                                         1 a
         2 2                   2 a
         3 3                   3 b

SQL> 
SQL> select * from t1 left outer join t2 on (t1.c1=t2.c1)
  2                   left outer join t3 on (t1.c1=t3.c1);

        C1 C2                 C1 C2                 C1 C2
---------- ---------- ---------- ---------- ---------- ----------
         1 1                                         1 a
         3 3                   3 b
         2 2                   2 a

SQL> 

-- Error here, one table can not be outer-joined to more than 1 table
-- That is, same table can not have (+) attached to it twice.
SQL> select * from t1, t2, t3 where
  2  t1.c1=t2.c1(+) and t3.c2=t2.c2(+);
t1.c1=t2.c1(+) and t3.c2=t2.c2(+)
     *
ERROR at line 2:
ORA-01417: a table may be outer joined to at most one other table