SA0109 : Avoid joining with subquery which has a TOP clause

Improper use of the TOP clause in subqueries can lead to unexpected results in SQL queries.

Description

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:

SQL
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.

How to fix

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:

SQL
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;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1SELECT c.CustomerID, c.Name, o.OrderID, o.OrderDate
2FROM Customers c
3JOIN (
4    SELECT TOP 1 OrderID, CustomerID, OrderDate
5    FROM Orders
6    WHERE OrderDate >= '2024-01-01'
7    ORDER BY OrderDate DESC
8) o ON c.CustomerID = o.CustomerID;

Analysis Results
  Message Line Column
1 SA0109 : Avoid joining with subquery which has a TOP clause. 4 11
See Also

Other Resources