G-3190

🆓

Error

Avoid using NATURAL JOIN.

SQL • Check

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 10 mins

Local-high
Database Support: OracleDB (v9.0.1 +)
PostgreSQL (v7.0.3 +)

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

Non-Compliant Example

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

No data found

Compliant Solution - ★★★★★

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
...

References

same as plsql:NaturalJoinUsageCheck
same as Trivadis G-3190