"No Join Predicate" when adding an additional condition - why?

Question

I have a, in general, very simple query and don't understand why the actual execution plan shows me a warning "No Join Predicate" right after the initial select on a "Nested Loops" node.

I think the query is pretty self-explanatory: I have Users and they have UserSubscriptions to Feeds (m:n) - I want to query all FeedItems from one Feed the user must be subscribed to, so this query does this very well:

select fi.Title, fi.Content, fi.Published
from [User] u 
inner join UserSubscription us on u.id = us.UserId
inner join Feed f on f.id = us.FeedId
inner join FeedItem fi on fi.FeedId = f.Id
where u.EMailAddress = '[email protected]'
and f.id = 3
and fi.Inserted > getdate() - 30

The interesting part is that there is no warning as long as i leave out this condition:

and f.id = 3

As soon as I remove this, the warning about the missing join predicate disappears. I don't understand the cause for this warning here.

Any help understanding this would be greatly appreciated!

Thanks b.

Answer 1

The reason you don't need to JOIN on the Feed table is because:

1. f.id = us.FeedId = fi.FeedId
2. The f (Feed) table isn't used/necessary anywhere else in the query (SELECT or WHERE)

Here's a more optimized query:

select fi.Title, fi.Content, fi.Published
from [User] u 
inner join UserSubscription us on u.id = us.UserId and us.FeedId = 3
inner join FeedItem fi on fi.FeedId = us.FeedId
where u.EMailAddress = '[email protected]'
and fi.Inserted > getdate() - 30

By limiting it to a particular FeedId earlier, you keep your dataset smaller, and therefore faster. The optimizer may change your query to this for you; I'm not sure.