Skip to content

BSL210 — Using a logical "OR" in the "WHERE" section of a query

← All rules

Summary

Using a logical "OR" in the "WHERE" section of a query

Identifiers

Field Value
Rule code BSL210
Compatible alias LogicalOrInTheWhereSectionOfQuery
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags query, performance, standard

Behavior

  • The public identifier BSL210 and alias LogicalOrInTheWhereSectionOfQuery are stable.
  • The rule reports the cases documented on this page.
  • Suppressions and project configuration are applied before publication.
  • The rule requires neither an external analyzer nor network access.

Configuration and suppression

BSL### is the primary stable identifier. The compatible alias is accepted in select, ignore, and compatible block suppression comments.

[tool.onec-hbk-bsl]
select = ["BSL210"]
ignore = ["LogicalOrInTheWhereSectionOfQuery"]

All three suppression families support both a current line and a range. When an opening comment follows code, it affects only that line. Use any one form:

  • noqa:
Value = "example";  // noqa: BSL210
  • bsl-disable:
Value = "example";  // bsl-disable: BSL210
  • compatible BSLLS form:
Value = "example";  // BSLLS:LogicalOrInTheWhereSectionOfQuery-off

When the same opening comment is on a line by itself, it starts a range. Close it with the matching marker from the same family:

// noqa: BSL210
// code without this diagnostic
// noqa-enable: BSL210

// bsl-disable: BSL210
// code without this diagnostic
// bsl-enable: BSL210

// BSLLS:LogicalOrInTheWhereSectionOfQuery-off
// code without this diagnostic
// BSLLS:LogicalOrInTheWhereSectionOfQuery-on

To disable the rule until the end of the file, omit the closing noqa-enable, bsl-enable, or BSLLS:…-on marker.

Opening and closing markers must belong to the same family.

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