AP 23 - Avoid the natural join clause

Severity

Type

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