Skip to content

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:

-- Example of problematic query
SELECT * FROM Orders o
JOIN (SELECT TOP 5 * FROM Customers ORDER BY CustomerID) c
ON 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:

-- Example of corrected query
SELECT o.*, c.*
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerID
FETCH 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.

1 hour per issue.

Design Rules, Bugs

There is no additional info for this rule.

SELECT c.CustomerID, c.Name, o.OrderID, o.OrderDate
FROM Customers c
JOIN (
SELECT TOP 1 OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= '2024-01-01'
ORDER BY OrderDate DESC
) o ON c.CustomerID = o.CustomerID;
  Message Line Column
1 SA0109 : Avoid joining with subquery which has a TOP clause. 4 11

Analysis Rules