G-3190
🆓Error
Avoid using NATURAL JOIN.
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;Issues
| Line | Column | Message |
|---|---|---|
| 5 | 1 | Using NATURAL JOIN. |
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 Trivadis G-3190
- same as plsql:NaturalJoinUsageCheck