“No Join Predicate” when adding an additional condition - why?
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 = 'firstname.lastname@example.org' 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!
The reason you don't need to JOIN on the Feed table is because:
- f.id = us.FeedId = fi.FeedId
- 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@example.com' 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.