“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 = '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.


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 = '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.

Need Your Help

How does “\x” work in a String?

c++ c hex

I'm writing a C/C++ program that involves putting a hex representation of a number into a string and I'm confused as to how \x works. I've seen examples where people have written things such as "\...

Look at the last edited information of a Plist file

iphone plist editing

Is there any way you can get the last edited time off a file? I have a pList on a server that download to my app. I would like to look at the time it was last edited. Do I have to implement a speci...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.