SA0051 : The query is missing a join predicate. This may affect or result more than expected rows
The topic describes the SA0051 analysis rule.
The query is missing a join predicate. This may affect or result more than expected rows
The rule checks the T-SQL code for queries having joined tables and missing join a predicate for one of the tables. It identifies the joined table sources which do not have any column referenced neither in the join conditions nor in the WHERE clause. Without a join predicate, the query result will include the Cartesian product of all rows.
The Cartesian products also known as Cross products may result significant performance overhead and are most commonly caused by missing join predicates for any of the joined tables in the ON or the WHERE clauses.
To correct this issue, ensure that your queries are correctly formed and that there are predicates for all joined tables.
The rule has a Batch scope and is applied only on the SQL script.
Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.
The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
There is no additional info for this rule.
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 31 , table2 32WHERE 1 = 2 -- IGNORE:SA0051 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;
|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|
© Ubitsoft Ltd. All Rights Reserved.