LogicalOrInTheWhereSectionOfQuery.md

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

Description

Не следует использовать ИЛИ в секции ГДЕ запроса. Это может привести к тому, что СУБД не сможет использовать индексы таблиц и будет выполнять сканирование, что увеличит время работы запроса и вероятность возникновения блокировок. Вместо этого следует разбить один запрос на несколько и объединить результаты.

Примеры

For example, query:

SELECT Goods.Description FROM Catalog.Goods AS Goods 
WHERE Code = "001" OR Cost = 10

should instead of a query:

SELECT Goods.Description FROM Catalog.Goods AS Goods 
WHERE Code = "001" 

UNION ALL

SELECT Goods.Description FROM Catalog.Goods AS Goods 
WHERE Cost = 10

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

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

Correct:

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

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

WHERE
    Table.Field IN (&Value)

Incorrect:

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

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

WHERE
     Table.Field1 = &Value1

UNION ALL

WHERE
     Table.Field2 = &Value1

Note: it is not always possible to replace OR with UNION ALL, make sure the result is really the same as with OR before applying.

2. Additionally, the 'OR' operator can be used without restriction.

Correct:

WHERE
    Table.Filed1 = &Value1 // Main condition (use index)
    AND // Addition condition (can use OR)
    (Table.Filed2 = &Value2 OR Table.Filed3 = &Value3)

Correct:

WHERE
    (Table.Filed1 = &Value1 OR Table.Filed1 = &Value2)
    AND
    (Table.Filed2 = &Value3 OR Table.Filed2 = &Value4)

because can be rewritten using 'IN' (no special rewriting needed, can be left as is):

WHERE
    Table.Field1 IN (&Value1)   // Main condition
    AND Table.Field2 IN (&Value2) // Additional condition (or vice versa)

Sources

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