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.

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.