Rule Definition
- ANSI syntax allow a clear separation between joins clause and the where clause restrictions. The ANSI notation makes the relations between the tables explicit, and saves you from coding equality tests for join conditions in the WHERE clause. Support for full outer joins also eliminates the need for complex workarounds to do those queries (With old syntax some construction can lead to an unexpected behavior). Moreover ANSI joins allows having optimization hints. And last, this historic syntax notation will probably be deprecated in a future release

Remediation
Transform the queries including Join predicats with the use of the ANSI syntax. As a consequence Reconsider tthe statement. For that check the business rule(s) to fully understand the objective of the statement in term of result set. Use ANSI Join syntax notation to rewrite the part of the code.

Violation Code Sample

cross join:
SELECT last_name, department_name
FROM employees, departments;

natural join:
SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;

USING clause usage:
SELECT employee_id, last_name,
employees.department_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

ON clause usage:
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);

other sample:
SELECT employee_id, city, department_name
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;

LEFT Outer Join sample:
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;

right outer JOIN:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);

Fixed Code Sample

cross join
SELECT last_name, department_name
FROM employees
CROSS JOIN departments;

natural join:
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations;

USING clause usage:
SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id);


ON clause usage:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

other sample
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

LEFT Outer Join sample:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

right outer JOIN:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

sample Outer JOIN:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Oracle SQL Student Guide Volume 1