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

sql-server, t-sql

Solution

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 = 'xxx@xxx.xx'
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.

Problem

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 = 'xxx@xxx.xx' 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.

Original source