Home

Description

The rule "Native SQL joins should be used" states that when writing PL/SQL code, native SQL joins should be used instead of PL/SQL constructs such as loops and cursors. Native SQL joins are more efficient and can improve the performance of the code. Additionally, using native SQL joins can help to make the code more readable and maintainable.

Key Benefits

• Performance: Native SQL joins offer superior performance compared to other join methods.
• Flexibility: Native SQL joins provide flexibility in terms of the types of joins that can be used.
• Scalability: Native SQL joins are highly scalable and can handle large data sets with ease.
• Reliability: Native SQL joins are reliable and can be used to ensure data integrity.

Non-compliant Code Example

BEGIN
  FOR order_table IN (SELECT * FROM orders) LOOP
    FOR order_items_table IN (SELECT * FROM order_items) LOOP  --Non compliant code (Used multiple for loop instead of joins)
      DBMS_OUTPUT.PUT_LINE('Order Id: ' || order_table.id || ', Order Amount: ' || order_table.amount || ', Order Items: ' || order_items_table.Quantity);
    END LOOP;
  END LOOP;
END;

Compliant Code Example

SELECT
    od.*, odi.*
FROM
    orders od
INNER JOIN order_items odi ON --Compliant code (Used native joins)
    odi.order_id = od.order_id
ORDER BY
    od.order_date DESC;