SA0051 : The query is missing a join predicate. This may affect or result more than expected rows |
![]() |
Ensure all joins in SQL queries include appropriate predicates to avoid Cartesian products and performance degradation.

When writing T-SQL queries involving multiple tables, a common problem is forgetting to include a join predicate for each table. This issue can lead to a Cartesian product, where all possible combinations of rows from the involved tables are returned. This not only causes performance issues but also might lead to incorrect query results.
— Example of problematic query with missing join predicate:
1SELECT * 2FROM Table1, 3 Table2, 4 Table3 5WHERE Table1.ID = Table2.ID
In the above query, Table3 is joined without a predicate. As a result, the database performs a Cartesian product, combining every row of Table3 with the result of the previous join between Table1 and Table2.
-
This can lead to significant performance degradation due to the large result set, increasing resource usage and query execution time.
-
The returned data might be incorrect or meaningless, as it includes combinations of rows that should not logically be joined.

Correct missing join predicates to prevent Cartesian products and improve query performance.
Follow these steps to address the issue:
-
Review the query to identify all joins between tables. Ensure each join operation includes a relevant join predicate using ON or USING clauses.
-
For each joined table, verify that there is at least one condition connecting the current table to another table using matching columns (typically primary and foreign keys).
-
Modify the query to include the missing predicates by specifying the correct column relationships in the ON clause.
For example:
1-- Corrected query with necessary join predicate 2SELECT * FROM Table1 3JOIN Table2 ON Table1.ID = Table2.ID 4JOIN Table3 ON Table2.ForeignKey = Table3.PrimaryKey;

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

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1DECLARE @StartProductID AS [int] 2 , @CheckDate AS [datetime] 3 4SET @StartProductID = 111 5SET @CheckDate = getdate( ); 6 7SELECT * 8FROM OrderDetails 9 10SELECT * 11FROM dbo.Orders 12INNER JOIN dbo.OrderDetails AS od 13ON 1 = 2 14WHERE od.OrderId = '00012345' 15 16 17SELECT * 18FROM [HumanResources].[Employee] AS e 19 , [Person].[Person] AS p 20 , [Person].[EmailAddress] AS ea 21WHERE p.[BusinessEntityID] = e.[BusinessEntityID] 22 23SELECT * 24FROM table1 25 , table2 26WHERE 1 = 2 27 28 29SELECT * 30FROM table1 -- IGNORE:SA0051 31 , table2 -- IGNORE:SA0051 32WHERE 1 = 2 33 34 35SELECT t1.*, t2.* 36FROM table_1 t1 37CROSS JOIN Table_2 t2; 38 39SELECT t1.*, t2.* 40FROM Table_1 t1, Table_2 t2; |

Message | Line | Column | |
---|---|---|---|
1 | SA0051 : Missing join predicate for table [Orders]. The query may affect more than expected number of rows. | 11 | 16 |
2 | SA0051 : Missing join predicate for table [ea]. The query may affect more than expected number of rows. | 20 | 39 |
3 | SA0051 : Missing join predicate for table [table1]. The query may affect more than expected number of rows. | 24 | 12 |
4 | SA0051 : Missing join predicate for table [table2]. The query may affect more than expected number of rows. | 25 | 12 |
5 | SA0051 : Missing join predicate for table [t1]. The query may affect more than expected number of rows. | 40 | 13 |
6 | SA0051 : Missing join predicate for table [t2]. The query may affect more than expected number of rows. | 40 | 25 |
