Skip to content

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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                 
...