how in clause in SQL server works

Question

I would like to know how comparisons for IN clause in a DB work. In this case, I am interested in SQL server and Oracle.

I thought of two comparison models - binary search, and hashing. Can someone tell me what method does SQL server follow.

Answer 1

IN is the same as EXISTS in SQL Server usually. They will give a similar plan. Saying that, IN is shorthand for OR..OR as JNK mentioned.

For more than you possibly ever needed to know, see Quassnoi's blog entry

FYI: The OR shorthand leads to another important difference NOT IN is very different to NOT EXISTS/OUTER JOIN: NOT IN fails on NULLs in the list

Answer 2

It depends on the query plan the optimizer chooses.

If there is a unique index on the column you're comparing against and you are providing relatively few values in the IN list in comparison to the number of rows in the table, it's likely that the optimizer would choose to probe the index to find out the handful of rows in the table that needed to be examined. If, on the other hand, the IN clause is a query that returns a relatively large number of rows in comparison to the number of rows in the table, it is likely that the optimizer would choose to do some sort of join using one of the many join methods the database engine understands. If the IN list is relatively non-selective (i.e. something like GENDER IN ('Male','Female')), the optimizer may choose to do a simple string comparison for each row as a final processing step.

And, of course, different versions of each database with different statistics may choose different query plans that result in different algorithms to evaluate the same IN list.

Answer 3

SQL Server's IN clause is basically shorthand for a wordier WHERE clause.

...WHERE column IN (1,2,3,4)

is shorthand for

...WHERE Column = 1
OR Column = 2
OR column = 3
OR column = 4

AFAIK there is no other logic applied that would be different from a standard WHERE clause.