Skip to content

BSL206 — Join with sub queries

← All rules

Summary

Join with sub queries

Identifiers

Field Value
Rule code BSL206
Compatible alias JoinWithSubQuery
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags query, performance

Behavior

  • The public identifier BSL206 and alias JoinWithSubQuery 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 = ["BSL206"]
ignore = ["JoinWithSubQuery"]

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: BSL206
  • bsl-disable:
Value = "example";  // bsl-disable: BSL206
  • compatible BSLLS form:
Value = "example";  // BSLLS:JoinWithSubQuery-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: BSL206
// code without this diagnostic
// noqa-enable: BSL206

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

// BSLLS:JoinWithSubQuery-off
// code without this diagnostic
// BSLLS:JoinWithSubQuery-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

When writing queries, you should not use subquery joins. Only metadata objects or temporary tables should be joined to each other.

If the query contains joins with subqueries, then this can lead to negative consequences:

  • Very slow query execution with low load on server hardware
  • Unstable work of the request. Sometimes the query can work fast enough, sometimes very slow
  • Significant difference in query execution time for different DBMS
  • Increased query sensitivity to the relevance and completeness of sql statistics. After a complete update of statistics, the query may work quickly, but after a while it will slow down

Examples

An example of a potentially dangerous query using a subquery join:

SELECT *
FROM Document.Sales
LEFT JOIN (
   SELECT Field1 ИЗ InformationRegister.Limits
   WHERE Field2 In (&List)
   GROUP BY
   Field1
) BY Refs = Field1

Sources