Does WHERE IN use query performance corruption?

Question

I heard that using the IN clause can hurt performance because it does not use indexes properly. See the example below:

SELECT ID, Name, Address 
FROM people
WHERE id IN (SELECT ParsedValue FROM UDF_ParseListToTable(@IDList))

Is it better to use the form below to get these results?

SELECT ID,Name,Address
FROM People as p
INNER JOIN UDF_ParseListToTable(@IDList) as ids
ON p.ID = ids.ParsedValue

Does it depend on which version of SQL Server you are using? If so, which ones are affected?

Answer 1

Yes, assuming relatively large datasets.

Better to use EXISTSfor large data sets. I am following this and have noticed improvements in runtime.

, IN vs. EXISTS. : http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Answer 2

- , , " ". . , , / .

, - , , , , . , .

:

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/ http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/

Answer 3

, IN Table Variable, , .

, , , .

-, IN , . @IDList, , , @IDList1, @IDList2, @IdList3...., IN .

, IN EXISTS - , .

Answer 4

IN , SQL Server , IF, . , UDF . , , . Google UDF Performance .

Answer 5

, WHERE X IN (@variable) WHERE X = @variable (.. ).

, , , .