LogicalOrInTheWhereSectionOfQuery.md

Using a logical "OR" in the "WHERE" section of a query (LogicalOrInTheWhereSectionOfQuery)

Type	Scope	Severity	Activated by default	Minutes to fix	Tags
Code smell	BSL	Major	Yes	15	sql performance standard

Description

Do not use OR in the WHERE section of the query. This can lead to the DBMS being unable to use the indexes of the tables and will perform scans, which will increase the query time and the likelihood of locks occurring. Instead, you should split one query into several and combine the results.

For example, the query

SELECT Item.Name FROM Directory.Products AS Item
WHERE Article = "001" OR Price = 10

should be replaced with a query

SELECT Product.Name FROM Directory.Products AS Product WHERE Article = "001"
UNION ALL
SELECT Product.Name FROM Directory.Products AS Product WHERE Price = 10

Important - the current diagnostic implementation triggers any OR in the WHERE section and may give false positives for some conditions.

1. In the main condition, the OR operator can be used only for the last used or the only index field, when the OR operator can be replaced with the IN operator.

CORRECT:

WHERE
     Table.Field = &Value1
     OR Table.Field = &Value2

since can be rewritten using the IN operator (you don't need to rewrite it specifically, you can leave it as it is):

WHERE
    Table.Field IN (&Value)

WRONG:

WHERE
     Table.Field1 = &Value1
     OR Table.Field2 = &Value2

cannot be overwritten with IN, but can be overwritten with UNION ALL (each field Field1 and Field2 must be indexed):

WHERE
    Table.Field1 = &Value1

ОБЪЕДИНИТЬ ВСЕ

WHERE
    Table.Field2 = &Value1

Note: replacing OR with UNION ALL is not always possible, make sure the result is indeed the same as OR before use.

2. In an additional condition, the OR operator can be used without restrictions.

CORRECT 1:

WHERE
     Table.Field1 = &Value1 // Main condition (uses index)
     AND // Additional condition (you can use OR)
     (Table.Field2 = &Value2 OR Table.Field3 = &Value3)

CORRECT 2:

WHERE
     (Table.Field1 = &Value1 OR Table.Field1 = &Value2)
     AND
     (Table.Field2 = &Value3 OR Table.Field2 = &Value4)

since can be rewritten using the IN operator (you don't need to rewrite it specifically, you can leave it as it is):

WHERE
     Table.Field1 B (&Values1) // Main condition
     AND Table.Field2 B (&Values2) // Additional condition (or vice versa)

Examples

Sources

• Standard - Effective Query Conditions, Clause 2
• Using Logical OR in Conditions - Typical Causes of Suboptimal Query Performance and Optimization Techniques
• Interesting analysis of SQL queries in various DBMS (not about 1C) - Article on Habr

Snippets

Diagnostic ignorance in code

// BSLLS:LogicalOrInTheWhereSectionOfQuery-off
// BSLLS:LogicalOrInTheWhereSectionOfQuery-on

Parameter for config

"LogicalOrInTheWhereSectionOfQuery": false