"NATURAL JOIN" queries should not be used

NATURAL JOIN is a type of equi-join which implicitly compares all identically-named columns of the two tables. While this a feature which may seem convenient at first, it becomes hard to maintain over time.

Consider an EMPLOYEE table with the columns FULL_NAME, and DEPT_ID, and a DEPARTMENT table with the columns DEPT_ID, and NAME. A natural join between those tables will join on the DEPT_ID column, which is the only identically-named column.

But, if a new NAME column is later added to the EMPLOYEE table, then the join will be done on both DEPT_ID and NAME. Natural joins make simple changes such as adding a column complicated and are therefore better avoided.

Noncompliant Code Example

BEGIN
  SELECT *
  INTO employeeArray
  FROM employee
  NATURAL JOIN departement; -- Non-Compliant, the join predicate is implicit
END;
/

Compliant Solution

BEGIN
  SELECT *
  INTO employeeArray
  FROM employee
  JOIN departement
  USING (dept_id);  -- Compliant, explicit join predicate
END;
/