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;
/
