Oracle: Outer Joins - Old and New Syntax

LEFT OUTER JOIN

OLD:
SELECT T1.C, T2.C
FROM T1, T2
WHERE T1.C = T2.C (+)

NEW:
SELECT T1.C, T2.C
FROM T1 LEFT OUTER JOIN T2
ON T1.C = T2.C

---------------------------------------

RIGHT OUTER JOIN

OLD:
SELECT T1.C, T2.C
FROM T1, T2
WHERE T1.C(+) = T2.C

NEW:
SELECT T1.C, T2.C
FROM T1 RIGHT OUTER JOIN T2
ON T1.C = T2.C

---------------------------------------

FULL OUTER JOIN

NEW:
SELECT T1.C, T2.C
FROM T1 FULL OUTER JOIN T2
ON T1.C = T2.C

ANSI syntax for SQL in ORACLE is recommended by Oracle. It supports various new features like outer joining with more than one table and as we see Partitioned Outer Joins, but ANSI syntax also comes with several limitations, major one is that it cannot be used in fast refreshable materialized views

Related Posts
- Fill Gaps in Sparse Data - Partitioned Outer Join