SQL Server IN vs = in query(for one value)

Question

First of all, sorry for the vague topic name(I could not think of something better).

An SO answer got me to thinking on whether there really is any performance impact when I chuck = for an IN.

select 1 id, 'abc' name into #a union 
select 2, 'abcd' union 
select 3, 'abcde' 

select * from #a where id = 1 ------ Query 1
select * from #a where id in (1)------ Query 2

I checked the query plans for both the queries and found them to me same(Maybe the example was simplistic probably that's why).

But am I violating any performance best practice when using an IN? Sure there must be some overheads involved with that, as an IN can handle multiple items and even SELECTS. I know IN offers a lot more in terms of features, but for cases when there are multiple records for id = 1,

select * from #a where id = (SELECT id from #a where id = 1)------ Query 3
select * from #a where id in (SELECT id from #a where id = 1)------ Query 4

query3 would fail while query4 would work. Thus I would want to replace = with an IN in future in my code. But I want to know of any potential trade-offs.

EDIT I am just interested in learning how(differently?) the SQL engine treats these statements.

Natiha

Usually you'd rewrite queries 3 and 4 with a join anyway, so you wouldn't need the IN or =. – Despoliation

Consider it just an example :) – Natiha

"=" operator would be faster than "IN". If you want to check use SET STATISTIC TIME IO ON – Sprinkling

Why is what I want to know. – Natiha

Answer 1

Ignoring the performance aspect of your question if I may, there is a design issue here. You should code to your intention so that when someone else reads your code they have a better chance of understanding what you were trying to achieve. Do you expect SELECT id from #a where id = 1 to return exactly one record? If so then use '='. If you expect it to return zero or more then '=' would be wrong.

Attenuate

Agree. I had actually asked this question out of curiosity on whether the SQL Engine on a much lower level, actually treats an IN and a = differently. My question I must reiterate is purely to understand how the engine works. – Natiha

The performance side of this is much more complex. The '=' operator is the '=' operator, nice and predictable, but 'IN' could manifest itself in query plans in many different ways, depending on many different factors. – Attenuate

Answer 2

Index efficiency is the only consideration between "=" and "in" to my knowledge. So if you want to take advantage of a index, you would need to use an "=".

The "in" statement is nonsargable. "=" is sargable.

Query 3 and 4 would probably do additional table scans in the subquery, so you would lose efficiency compared to a join.

Guck

I disagree. I created clustered index on id column and tested and saw that both the queries were using Index Seek operation. So yes, for query 4 too index IS used – Natiha

"in" and "=" are operators, it is incorrect to say that operators are sargable or not-sargable. Expressions are sargable or not, depending on many factors. – Attenuate

Answer 3

Generally IN will be broken up into multiple = predicates and those will be used to satisfy the query. When used on an index (assuming) like in your example the individual predicates will be used for the index seek operation. Try it yourself. Look at the seek predicates in the 'Index Seek' operation of your execution plan.

Nondisjunction