CODEWITHSUNDEEP
sql-serverdatabase-performance
YvesR
YvesR

Reputation: 6222

SQL Performance compare IN to COUNT

I am thinking about performance over all and came to the question how the fastest way to get rows from a table for that matter:

SELECT *
FROM table a
WHERE a.id IN (SELECT id FROM table b)

OR

SELECT *
FROM table a
WHERE (SELECT COUNT(id) FROM table b WHERE b.id = a.id)>0

Running the profiler on my dev machine don't make a big difference, but I don't have huge data tables to compare that issue.

So it would be cool if someone can say which way is better and why. I read once that IN split the values in many OR like IN (1,2,3,4) will be 1 OR 2 OR 3 OR 4 but I am not sure if this is in general and the same if I query a sub select. In this case I would guess that the COUNT will be faster...

Upvotes: 1

Views: 114

Answers (2)

Iwade Rock
Iwade Rock

Reputation: 174

It's not a good idea to obtain a COUNT when you need to determine if something EXISTS. COUNT should be used when you need to determine a quantity. COUNT will have to read all rows (either in the table or an index) to determine if they qualify, whereas EXISTS can stop processing as soon as it finds the first occurrence. Answer #1 supports this.

Upvotes: 3

Devart
Devart

Reputation: 121952

Please see results on real DB (table with 100.000 records).

1. EXISTS - 803ms
2. COUNT - 814ms
3. IN - 870ms

Profiler

Upvotes: 2

Related Questions