Home

NATURAL JOIN queries should not be used

Description

    The rule "NATURAL JOIN queries should not be used" states that when writing PL/SQL code, the use of NATURAL JOIN queries should be avoided. NATURAL JOIN queries are a type of SQL query that combines two or more tables by matching all columns with the same name. This type of query can lead to unexpected results and should be avoided in favor of explicit JOIN queries that specify the columns to be joined.

Key Benefits

  • No Duplicate Data: NATURAL JOIN queries should not be used because they can result in duplicate data being returned.
  • No Unnecessary Joins: NATURAL JOIN queries should not be used because they can cause unnecessary joins to be created.
  • No Unnecessary Columns: NATURAL JOIN queries should not be used because they can cause unnecessary columns to be returned.
  • No Unnecessary Data: NATURAL JOIN queries should not be used because they can return unnecessary data.

 

Non-compliant Code Example

SELECT
    od.*, odi.*
FROM
    orders od
NATURAL JOIN order_items odi; --Non compliant code (NATURAL JOIN query is used)

Compliant Code Example

SELECT
    od.*, odi.*
FROM
    orders od
INNER JOIN order_items odi ON --Compliant code (NATURAL JOIN query is not used)
    odi.order_id = od.order_id
ORDER BY
    od.order_date DESC;
Visual Expert 2024
 VEPLSQLRULE79