The join in question is:
After removing all the redundant joins and table accesses that are not needed to compute the simple final query:
...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:
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.
By SQLkiwi ♦ 6.6k answered Jan 09, 2017 at 04:43 PM