Why is there an extra predicate with an IN clause? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Why is there an extra predicate with an IN clause?

sql-server sql-server-2019 add tag

Forrest

Consider the following 

<https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ed52053f320f9d35f575efe6c14a0e59>

```
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

SELECT *
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.

![Capture.PNG](/image?hash=3a07dc86dd8c947d2d627398c32ffe909891d6dfc19b94c74d75caf77962acab)

![surprise predicate.png](/image?hash=37aaad1a7d2f2b689e903857602b8dbf1f9f45b99e3bb7e3edbbc5ccaf432173)

Shouldn't the Seek Predicate be enough to guarantee correctness? What's the purpose of this extra predicate?

Top Answer

Martin Smith

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/)

1 Answer