Consider the following
CREATE TABLE #A (ID int primary key)
CREATE TABLE #B (ID int primary key)
INSERT #A SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0) FROM sys.columns
INSERT #B SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0) FROM sys.columns
FROM #A a
JOIN #B b
ON a.ID = b.ID
WHERE a.ID IN (1,10)
There are two seeks into #A to find the IDs, each followed by a seek in #B to find the matching ID. However, there's an extra predicate in the seek on #B.
Shouldn't the Seek Predicate be enough to guarantee correctness? What's the purpose of this extra predicate?
The extra predicate on `#B` is added during query simplification by the "implied predicate" mechanism.
It sees that `a.ID IN (1,10)` and `a.ID = b.ID` so adds an implied predicate that `b.ID BETWEEN 1 AND 10`. A similar case is [discussed here](https://answers.sqlperformance.com/questions/2299/why-not-seek-predicate.html).
It could of course be more specific and just add an implied predicate of `b.ID IN (1,10)` but I guess the range is simple to implement irrespective of number of values in the `IN` clause and is good enough for restricting the rows read from `B` if doing a join type other than nested loops (i.e. `MERGE` join or `HASH` join).
For the nested loops plan in this example it is not useful. An example where this type of implied predicate *is* useful is [in the article here](https://www.brentozar.com/archive/2017/11/implied-predicate-partition-elimination/)