AP 23 - Avoid the natural join clause
Severity
WarningType
Readability
Correctness
Problem
The SQL natural join is a type of equi-join that implicitly combines tables based on columns with the same name and type. The join predicate arises implicitly by comparing all columns in both tables that have the same column names in the joined tables.
The following issues may arise:
- You may accidentally join on the wrong columns, e.g. it is common to add a generic column create_date_time to tables. The natural join would include these column in the join which is most likely incorrect
- It takes the CBO extra time to parse the query to identify the join column candidates
- It reduces readability by introducing a non-standard syntax
SELECT col1
FROM d1
NATURAL
INNER
JOIN d2
Solution
Use an ANSI-92 compliant join syntax
SELECT col1
FROM d1
INNER
JOIN d2
ON(d1.id=d2.id)
Legitimate use of the anti pattern
None