When ANSI join comes to play?

ANSI join has been around for many years.
There are a couple of situations better off using ANSI join rather than Oracle native join.

1. Join many tables.

Reason:

Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidentally create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.

2. Outer join.

Reason:

Basically (+) is severely limited compared to ANSI joins. ANSI syntax is cleaner - you are not going to normal join if you forget (+) in some multi-column outer join.
In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.


Example:

SELECT *
  FROM table_a a 
  JOIN table_b b 
    ON a.col1 = b.col1
  JOIN table_c c
    ON b.col2 = c.col2;

Example 2:

SELECT d.department_id, e.last_name, j.job_name
  FROM departments d 
  LEFT OUTER JOIN employees e
    ON d.department_id = e.department_id
  LEFT OUTER JOIN job j
    ON e.job_id = j.job_id
;