Click or drag to resize

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.

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0051

Remarks

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

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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
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

 MessageLineColumn
1SA0051 : Missing join predicate for table [Orders]. The query may affect more than expected number of rows.1116
2SA0051 : Missing join predicate for table [ea]. The query may affect more than expected number of rows.2039
3SA0051 : Missing join predicate for table [table1]. The query may affect more than expected number of rows.2412
4SA0051 : Missing join predicate for table [table2]. The query may affect more than expected number of rows.2512
5SA0051 : Missing join predicate for table [t1]. The query may affect more than expected number of rows.4013
6SA0051 : Missing join predicate for table [t2]. The query may affect more than expected number of rows.4025
See Also

Other Resources