Whenever I open a plan, I keep an eye out for plan warnings.  These are sometimes really useful, but at times they may contain some inconsequential or incorrect information.  One in particular is the “No Join Predicate”.  Typically, this would be cause for alarm since this is an indicator of a cartesian join.  This would be something along the lines of doing this:

SELECT * FROM SalesLT.Product, SalesLT.ProductCategory

Note there is no join between the two tables.  As such the plan has a warning in it:

There are cases where there is a valid join, but the plan warning gets reported.  This typically happens when a query parameter is used and SQL Server replaces a join condition on a nested loop with the parameter that is shared across the two tables.  You’ll see something along the lines of this rather than a join in the predicate:

Top of nested loop:

 

Bottom of nested loop:

If you see this, it is likely a false positive and you can probably ignore it.