Home

Description

The rule "Nested subqueries should be avoided" states that when writing PL/SQL code, nested subqueries should be avoided whenever possible. Nested subqueries are subqueries that are nested within other subqueries, and can lead to complex and inefficient code. Instead, it is recommended to use joins or correlated subqueries to achieve the same result with simpler and more efficient code.

Key Benefits

• Avoiding complexity: By avoiding nested subqueries, you can reduce the complexity of your query and make it easier to read and understand.
• Improving performance: Nested subqueries can be expensive to execute and can slow down query performance. Avoiding them can help improve performance.
• Simplifying maintenance: By avoiding nested subqueries, you can make your query easier to maintain and modify.

Non-compliant Code Example

SELECT
    od.*
FROM
    orders od
Where od.Id in (Select odi.orderId From order_items odi)        --Non compliant code (Nested sub-queries used)

Compliant Code Example

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