Probe Residual vs Join Predicate

Question

What is the difference between a Residual and a Join predicate?

If I haven’t missed a subtlety they are exactly the same. Why confuse novices by using two different words for the same thing?

Answer 1

These are distinct concepts. We're right into the details of of how SQL Server plans its queries, not the place for the faint hearted! But very interesting, you won't be a "noob" for long if you're studying this area.

This article describes how some predicates can be interpreted to "seek", first excluding many rows, so that only a few need to be examined to determine if they match the remainder of the predicate - the residual predicate.

This article describes how joins are processed, and how the details of the query can lead to the decision as to whether there's a join predicate, radically affecting the performance.