SA0051 : The query is missing a join predicate. This may affect or result more than expected rows

The topic describes the SA0051 analysis rule.

Message

The query is missing a join predicate. This may affect or result more than expected rows

Description

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.

Note Note

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.
How to fix

To correct this issue, ensure that your queries are correctly formed and that there are predicates for all joined tables.

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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;
Analysis Results
  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
See Also

Other Resources

Analysis Rules