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.

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:
-
Identify the part of the query where the TOP clause is used within a subquery.
-
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.
-
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.

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

Message | Line | Column | |
---|---|---|---|
1 | SA0109 : Avoid joining with subquery which has a TOP clause. | 4 | 11 |
