SA0109 : Avoid joining with subquery which has a TOP clause
Introduction
Section titled “Introduction”Improper use of the TOP clause in subqueries can lead to unexpected results in SQL queries.
Description
Section titled “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:
-- Example of problematic querySELECT * FROM Orders oJOIN (SELECT TOP 5 * FROM Customers ORDER BY CustomerID) cON 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
Section titled “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:
-- Example of corrected querySELECT o.*, c.*FROM Orders oJOIN Customers c ON o.CustomerID = c.CustomerIDORDER BY c.CustomerIDFETCH FIRST 5 ROWS ONLY;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”SELECT c.CustomerID, c.Name, o.OrderID, o.OrderDateFROM Customers cJOIN ( SELECT TOP 1 OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate >= '2024-01-01' ORDER BY OrderDate DESC) o ON c.CustomerID = o.CustomerID;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0109 : Avoid joining with subquery which has a TOP clause. | 4 | 11 |