G-3190: Avoid using NATURAL JOIN.

Major

Changeability, Reliability

Reason

A natural join joins tables on equally named columns. This may comfortably fit on first sight, but adding logging columns to a table (changed_by, changed_date) will result in inappropriate join conditions.

Example (bad)

SELECT department_name 
      ,last_name 
      ,first_name 
  FROM employees NATURAL JOIN departments
 ORDER BY department_name 
         ,last_name;
DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME         
------------------------------ ------------------------- --------------------
Accounting                     Gietz                     William             
Executive                      De Haan                   Lex                 
…

ALTER TABLE departments ADD modified_at DATE DEFAULT ON NULL SYSDATE;
ALTER TABLE employees ADD modified_at DATE DEFAULT ON NULL SYSDATE;

SELECT department_name 
      ,last_name 
      ,first_name 
  FROM employees NATURAL JOIN departments
 ORDER BY department_name 
         ,last_name;

No data found

Example (good)

SELECT d.department_name 
      ,e.last_name 
      ,e.first_name 
  FROM employees   e
  JOIN departments d ON (e.department_id = d.department_id)
 ORDER BY d.department_name 
         ,e.last_name;

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME         
------------------------------ ------------------------- --------------------
Accounting                     Gietz                     William             
Executive                      De Haan                   Lex                 
…