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
select department_name
      ,last_name
      ,first_name
  from employees
natural join departments
 order by department_name
      ,last_name;

1
2
3
4
5
DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME         
------------------------------ ------------------------- --------------------
Accounting                     Gietz                     William             
Executive                      De Haan                   Lex                 
...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;

1
No data found

Example (good)

1
2
3
4
5
6
7
8
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;

1
2
3
4
5
DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME         
------------------------------ ------------------------- --------------------
Accounting                     Gietz                     William             
Executive                      De Haan                   Lex                 
...