Uploaded image for project: 'Rules Repository'
  1. Rules Repository
  2. RSPEC-2521

"NATURAL JOIN" queries should not be used

    XMLWordPrintable

    Details

    • Type: Code Smell Detection
    • Status: Active
    • Resolution: Unresolved
    • Labels:
    • Message:
      Refactor this query to join explicitly on specific columns.
    • Default Severity:
      Major
    • Impact:
      Low
    • Likelihood:
      High
    • Default Quality Profiles:
      Sonar way
    • Legacy Key:
      NaturalJoinUsageCheck
    • Covered Languages:
      PL/SQL
    • Remediation Function:
      Constant/Issue
    • Constant Cost:
      15min

      Description

      NATURAL JOIN is a type of equi-join which implicitly compares all identically-named columns of the two tables. While this a feature which may seem convenient at first, it becomes hard to maintain over time.

      Consider an EMPLOYEE table with the columns FULL_NAME, and DEPT_ID, and a DEPARTMENT table with the columns DEPT_ID, and NAME. A natural join between those tables will join on the DEPT_ID column, which is the only identically-named column.

      But, if a new NAME column is later added to the EMPLOYEE table, then the join will be done on both DEPT_ID and NAME. Natural joins make simple changes such as adding a column complicated and are therefore better avoided.

      Noncompliant Code Example

      BEGIN
        SELECT *
        INTO employeeArray
        FROM employee
        NATURAL JOIN departement; -- Non-Compliant, the join predicate is implicit
      END;
      /
      

      Compliant Solution

      BEGIN
        SELECT *
        INTO employeeArray
        FROM employee
        JOIN departement
        USING (dept_id);  -- Compliant, explicit join predicate
      END;
      /
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ann.campbell.2 Ann Campbell
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: