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

Answers


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.