JoinWithSubQuery.md

Join with sub queries (JoinWithSubQuery)

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)