Home

Large item lists should not be used with IN clauses

Description

    The rule "Large item lists should not be used with IN clauses" in PL/SQL code states that when using the IN clause in a SQL statement, it is best to avoid using large item lists as this can lead to performance issues. This is because the IN clause will cause the database to scan through the entire list of items, which can be time consuming if the list is large. It is better to use other clauses such as EXISTS or JOIN to achieve the same result.

Key Benefits

  • Improved Performance: Using IN clauses with large item lists can cause poor query performance due to the amount of data that needs to be processed.
  • Reduced Complexity: By avoiding the use of IN clauses with large item lists, you can reduce the complexity of your queries and make them easier to maintain.
  • Increased Flexibility: By avoiding the use of IN clauses with large item lists, you can increase the flexibility of your queries and make them easier to modify.

 

Compliant Code Example

SELECT
    NAME,
    ADDRESS,
    CREDIT_LIMIT
FROM
    CUSTOMERS
Where CREDIT_LIMIT IN (900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900); --Compliant code (IN clause is having less number of items than the default defined 1000 items)
Visual Expert 2024
 VEPLSQLRULE84