Replace IN clause with JOIN to @Table variable

Question

Could there any advantage in replacing an IN clause with a join to a table variable?

e.g. instead of using

SELECT * FROM TABLE 
WHERE Col1 IN ( 'VAL1', 'VAL2', 'VAL3', 'VAL4' )

use

DECLARE @VALUESET TABLE (VAL VARCHAR(4) NOT NULL PRIMARY KEY);
INSERT INTO @VALUESET 
SELECT 'VAL1' UNION ALL
SELECT 'VAL2' UNION ALL
SELECT 'VAL3' UNION ALL
SELECT 'VAL4';
SELECT * 
FROM TABLE AS T
JOIN @VALUESET AS V ON T.Col1 = V.VAL;

And add an index on Col1;

(might SQL Server possibly do something like this under the covers anyway?)

Answer 1

0

Depending on the numbers of rows involved in the tables being joined then yes, using a table variable, or indeed a temporary table, can have huge performance benefits over using WHERE .. IN (...). With careful thought on indexing then this can further ensure scalability performance.

I would certainly test all three options and allow for potential growth in data and how that might affect the number of options in the IN(..).

Personally I'd expect/prefer to use the JOIN method where there are more than a 3 or 4 values, purely from experience, 3 or 4 turns into 20 or 30 in a pretty short space of time

answered Mar 08 '12 at 10:38 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

@Tom Brown I do not think that your case has much to do with IN and JOIN. The real issue was using the string literals OR look-up values from a temporary table. (That is a totally separate debate). Hence, the same way you used the JOIN clause, you can use the IN clause for look-up table (Preferably a physical table OR a temporary table with possibly unique index on the look-up column) e.g.

SELECT * 
FROM TABLE AS T 
WHERE T.Col1 IN (SELECT V.VAL FROM @VALUESET AS V);

As far the performance is concerned, I am not going to say much about it as Gail's article (And the comments) has a good explanation. My personal experience is with proper indexing, when you do not want any output from the lookup table and (most importantly) if you are not sure of unique look-up values, then IN would be as good as JOIN (I prefer EXISTS over IN).

Moreover, I must highlight that sometimes, we do overlook the possibility of the duplicate values in the look-up table. Then a JOIN would return multiple rows and if we are aggregating the data, then it could be hard to sort out that the aggregations are not correct.

At the end, as @Fatherjack said that one has to TEST each option and use whatever seems to best for his/her environment.

Mar 08 '12 at 01:30 PM Usman Butt

(comments are locked)

Replace IN clause with JOIN to @Table variable

Follow this question

Related Questions