Join with sub queries (JoinWithSubQuery)¶

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

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¶

Standard: Restrictions on SubQuery and Virtual Table Joins (RU)

Snippets¶

Diagnostic ignorance in code¶

// BSLLS:JoinWithSubQuery-off
// BSLLS:JoinWithSubQuery-on

Parameter for config¶

"JoinWithSubQuery": false