SA0109 : Avoid joining with subquery which has a TOP clause

Using the TOP clause within subqueries that are joined can create issues. The TOP clause limits the number of rows returned by a query. When combined with joins, this can result in a misleading or partial data set, causing inaccurate query results and reports.

For example:

1-- Example of problematic query
2SELECT * FROM Orders o
3JOIN (SELECT TOP 5 * FROM Customers ORDER BY CustomerID) c
4ON o.CustomerID = c.CustomerID;

In this query, the join might include only a subset of the Customers data, potentially omitting important related records in Orders. This is because the TOP clause only retrieves a limited number of rows before the join operation.

• Can lead to incomplete sets of joined data, influencing the overall data aggregation and analysis.

• May result in inaccurate reports or incorrect business intelligence insights, affecting decision-making processes.

Rewrite queries to join directly with tables instead of using subqueries with the TOP clause to ensure an optimal execution plan and accurate results.

Follow these steps to address the issue:

1. Identify the part of the query where the TOP clause is used within a subquery.

2. Reconstruct the query to replace the subquery with direct joins on the involved tables. Ensure the join conditions match the logic of the original subquery.

3. Verify that the rewritten query returns the same number of records as the subquery with the TOP clause would return.

For example:

1-- Example of corrected query
2SELECT o.*, c.*
3FROM Orders o
4JOIN Customers c ON o.CustomerID = c.CustomerID
5ORDER BY c.CustomerID
6FETCH FIRST 5 ROWS ONLY;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources