Why does this say there is no join predicate?

My execution plan is posted on PasteThePlan.com. The second nested loop is warning that there is no join predicate. I'm still new to this so I'm confused. Is that because I am using a CTE to determine the values for two of the attributes? This presentation table is used in more that a few reports, and I don't want to create a second one for this very unique one.

Here's the query:

```
WITH Accumulators AS
(
SELECT DISTINCT AreaNum, SuperintendentNum, SuperName
FROM FI.SLAnalysis
WHERE SuperName LIKE '%ACCUM%'
)
SELECT
slard.[IO]
, slard.PhaseName AS PhaseName
, slard.JobNum AS JobNum
-- , slard.JobName AS JobName
, CASE
WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
THEN ac.SuperintendentNum
ELSE slard.SuperNum
END AS SuperNum
, CASE
WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
THEN ac.SuperName
ELSE slard.SuperName
END AS SuperName
, slard.AreaNum AS AreaNum
, slard.AreaName AS AreaName
, fp.WeekEndDate AS WeekEndDate

-- WTD Metrics
, slard.WeeklyRevenue AS WTDRevenue
, slard.WeeklyCost AS WTDCost
, slard.WeeklyRevenue - slard.WeeklyCost AS WTDGP
, CASE
WHEN slard.WeeklyRevenue = .01 THEN 0
ELSE slard.WeeklyRevenue
END AS WTDRatio
, slard.Weekly_PER AS WTDGPPer
, slard.WeeklyStandOvrheadAdmin AS WTDOverAdmin
, slard.WeeklyStandOvrheadEquip AS WTDOverEquip
, (slard.WeeklyRevenue
-slard.WeeklyCost
-slard.WeeklyStandOvrheadAdmin
-slard.WeeklyStandOvrheadEquip) AS WTDOHGP

--MTD Metrics
, slard.MTDRevenue AS MTDRevenue
, slard.MTDCost AS MTDCost
, slard.MTDRevenue - MTDCost AS MTD_GP
, CASE
WHEN slard.MTDRevenue 0 THEN 0
ELSE slard.MTDRevenue
END AS MTDRatio
, CASE
WHEN slard.MTDRevenue <> 0
THEN CAST(slar

It's happening here because the predicate slard.AreaNum IN ('40') is pushed to both ends of the Nested Loop Join operation.

I have a blog post about this same thing, but what it boils down to is that your WHERE clause matches a JOIN clause ON slard.AreaNum = ac.AreaNum

In these cases, the optimizer can push SARGable predicates to the the index access (seek or scan), because it's assured that whichever values come out will match.

This is called an implied predicate, and you can read more about them here:

Craig Freedman

Some Bozo

You can ignore the warning in this case.

Hope this helps!

StackExchange Database Administrators Q#197812, answer score: 11