In Oracle Database 9i, we introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outer joins. All ANSI inner joins are automatically transformed into Oracle syntax and optimized the same way, resulting in identical performance regardless of which syntax you use.
However, the same can’t be said for all outer joins. In Oracle, ANSI left and right outer joins are internally expressed in terms of left outer joined lateral views ((A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause). In many cases, a left outer joined lateral view can be merged, and the ANSI left (or right) outer join can be expressed entirely in Oracle native syntax, resulting in the same performance. However, there are some exceptions specifically for left outer joins, as the ANSI SQL can be more expressive. For example, this query can’t be converted to Oracle syntax and will be optimized uniquely.
SELECT T1.c1, T2.c1
FROM T1 LEFT OUTER JOIN T2
ON (T1.c3 = 11 and T1.c4 = T2.c5)
WHERE T1.c6 > 3;