Why am I getting a No Join Predicate when there is join criteria on all joins

avatar image By Tony Green 37 asked Jan 03, 2017 at 05:27 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 08, 2017 at 05:34 PM

I'm not sure on first quick glance, but I normally ignore these warnings, as long as I've verified the results are correct (they're meant to point out accidental cross joins). I would be more focused on the tempdb spill...

10|10000 characters needed characters left

1 answer: sort voted first

The join in question is:

 LEFT OUTER JOIN [dbo].[int_organization] AS [Facilities]
     ON [Facilities].[organization_nm] = [Assignment].[FacilityName]
     AND [Facilities].[category_cd] = 'F'

The predicate [Facilities].[category_cd] = 'F' is pushed down to the index seek on the inner side of the join. It is independent of the join, so is not considered a join predicate.

The predicate [Facilities].[organization_nm] = [Assignment].[FacilityName] refers to a column defined in the CTE xv_DeviceSessionAssignment as:

 CASE WHEN CHARINDEX('+', [InfoUnit].[Value]) > 0 
     THEN LEFT([InfoUnit].[Value], CHARINDEX('+', [InfoUnit].[Value]) - 1)
 END AS [FacilityName]

After removing all the redundant joins and table accesses that are not needed to compute the simple final query:

     [DLPAT].[patient_id] AS [PATIENTID]
     [xv_PatientSessions] AS [DLPAT]
     INNER JOIN [dbo].[int_product_access] AS [Access]
         ON [Access].[organization_id] = [DLPAT].[UNIT_ID];

...the remaining join predicate is simplified and moved to the Filter to the left of the Hash Match Right Outer Join on the top branch of the query:

Filter properties

With both original join conditions moved elsewhere in the plan, the remaining join is logically a cross-join. This happens to result in a warning, which can be safely ignored.

This is quite normal, especially when running a simple query against a complex view (or CTE) where not all the operations in the views/CTEs are actually necessary to return correct and complete results.

Even so, when you see a missing join predicate warning, it is important to check that your query has proper join predicates, and that no errors have been made by the query writer resulting in a cross product where none was intended. Most often this will be quite clear from the results (which will be incorrect) and/or an unexpectedly long runtime for the query.

Please also note that (non-recursive) CTEs are nothing more, from a query optimization perspective, than an inline view definition. If you were to write your query as views (on views etc) instead of as CTEs (that reference other CTEs) the result and execution plan would most likely be identical.

sp.png (35.2 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jan 09, 2017 at 04:43 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Jan 03, 2017 at 05:27 PM

Seen: 152 times

Last Updated: Jan 09, 2017 at 04:43 PM