Is there a difference between '=' and In?

Question

Is there any differences (perf) writting this request:

Select * from T where PK = 1

or this

Select * from T where PK in (1)

I believe not but i realy don't know how to dispay an execution plan that should assert my feeling.

Thx in advance

Answer 1

The IN expression:

Column in (Val1,Val2,Val3)

Is internally rewritten into something very similar to:

(Column = Val1 or Column = Val2 or Column = Val3)

You can tell this because, if you supply the name of a non-existent column, the number of errors reported (Invalid Column name 'Blah') is equal to the number of values in the IN list. Of course, such re-writing only occurs for lists of literal values. Subqueries (as @oezi says) are handled differently.

---

Of course, this particular optimization isn't documented, and it is always preferable to write the clearest code possible.

I'm not sure if there's an upper limit where it will not perform this expansion - it will certainly do it up to 100 values in the IN list (and I can't be bothered to type in more).

Answer 2

in your case: no difference

in general: you could write a subselect or inside of the braces, and = will fail if that subselect returns more than 1 row. also, you could write (1,2) (wich would be like a subselect that returns 2 rows), where = isn't possible, too.