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 (updated_by, updated) will result in inappropriate join conditions.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select department_name 
      ,last_name 
      ,first_name 
  from employee natural join department
 order by department_name 
         ,last_name;
DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME         
------------------------------ ------------------------- --------------------
Accounting                     Gietz                     William             
Executive                      De Haan                   Lex                 
…

alter table department add updated date default on null sysdate;
alter table employee add updated date default on null sysdate;

select department_name 
      ,last_name 
      ,first_name 
  from employee natural join department
 order by department_name 
         ,last_name;

No data found

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
select dept.department_name 
      ,emp.last_name 
      ,emp.first_name 
  from employee   emp
  join department dept using (department_id)
 order by dept.department_name 
         ,emp.last_name;

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