Skip to content

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:

SELECT *
FROM Table1,
Table2,
Table3
WHERE 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:

1.Review the query to identify all joins between tables. Ensure each join operation includes a relevant join predicate using ON or USING clauses.

2.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).

3.Modify the query to include the missing predicates by specifying the correct column relationships in the ON clause.

For example:

-- Corrected query with necessary join predicate
SELECT * FROM Table1
JOIN Table2 ON Table1.ID = Table2.ID
JOIN 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.

13 minutes per issue.

Design Rules, Bugs

There is no additional info for this rule.

DECLARE @StartProductID AS [int]
, @CheckDate AS [datetime]
SET @StartProductID = 111
SET @CheckDate = getdate( );
SELECT *
FROM OrderDetails
SELECT *
FROM dbo.Orders
INNER JOIN dbo.OrderDetails AS od
ON 1 = 2
WHERE od.OrderId = '00012345'
SELECT *
FROM [HumanResources].[Employee] AS e
, [Person].[Person] AS p
, [Person].[EmailAddress] AS ea
WHERE p.[BusinessEntityID] = e.[BusinessEntityID]
SELECT *
FROM table1
, table2
WHERE 1 = 2
SELECT *
FROM table1 -- IGNORE:SA0051
, table2 -- IGNORE:SA0051
WHERE 1 = 2
SELECT t1.*, t2.*
FROM table_1 t1
CROSS JOIN Table_2 t2;
SELECT t1.*, t2.*
FROM 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

Analysis Rules