Is there any faster way to match records with values present in a column in another table?

Question

I need to fetch values that are not present in a column in another table. Currently what i am using is

and [tPlayerTag].TagId NOT IN (select RequiredTagId from dbo.tPrize(NOLOCK) 
where RequiredTagId is not null)

This is present inside my where clause . It's working completely fine but i think it may slow down my query when dealing with a bigger table(tPrize). Is there any faster way to rewrite this logic ?

See this - useful post for your query https://stackoverflow.com/questions/173041/not-in-vs-not-exists — Mukesh Arora, Mar 08 '19 at 06:55

score 1 · Answer 1 · answered Mar 08 '19 at 05:55

you can use not exists

Choosing one over another, of course, depends on a situation: on volume of data that driven and driving queries return. In the case of [NOT] IN operator inner query (..where id in (select id from table )) is a driving query whereas in the case of [NOT] EXISTS outer query is a driving query. So if the sub-query (inner query) returns small amount of data because of either a table in the sub-query contains small number of rows or there is intensive filtering applied to the sub-query [NOT] IN operator may give better performance. If the sub-query returns large volume of data or the major filtering is happening in outer-query [NOT] EXISTS operator is preferable.

and [tPlayerTag].TagId NOT exists (select RequiredTagId from dbo.tPrize(NOLOCK) 
where RequiredTagId is not null)

score 1 · Answer 2 · answered Mar 08 '19 at 05:55

Inplace of in you can use exists and also inplace of not in you can not exists and try like this it will work.

Performance wise you have to use not exists

[tPlayerTag].TagId NOT exists (select RequiredTagId from dbo.tPrize(NOLOCK) 
where RequiredTagId is not null)

score 1 · Answer 3 · answered Mar 08 '19 at 13:18

I strongly advise you to use not exists rather than not in, because NULL values do not affect the result. You seem to know this is a problem because of your where clause.

The expression is:

NOT EXISTS (SELECT 1
            FROM dbo.tPrize p
            WHERE p.RequiredTagId = tPlayerTag.TagId
           )

For performance, you want an index on tPrize(RequiredTagId). This is the index that will make the performance more scalable.

Is there any faster way to match records with values present in a column in another table?

3 Answers3