DISTINCT is always implied in a subquery used in an IN clause?
select * from tab4 where a in (select b from tab4)
select * from tab4 where a in (select DISTINCT b from tab4)
The above 2 queries give exactly the same query plan and IO statistics, so it seems that the DISTINCT in the second query has no effect and is ignored by SQL Server. (I have a Hash Map (Partial Aggregate) statement in both queries which I assume is used for DISTINCT)
Is this true for all cases, or am I just hitting the edge?
source to share
DISTINCT
there is no logical difference here. Therefore, it is meaningless to say that this is meant or not.
At the implementation level, SQL Server knows this. Using DISTINCT
in a subquery to improve performance is superstitious. He does not do anything.
In fact, it is not clear to me why this would even help in all cases. Allocating a set can be costly. And SQL Server won't do this if it's not a good idea.
source to share