Should I use IN or NOT IN clause in a query with a huge amount of records?

Question

I need to fetch about 10 Million Records and avoid some of them passing a blacklist as parameter, I was thinking to use a NOT IN clause but since this kind of clauses makes the query so expensive which will be the best approach, the blacklist will contain about 20 records?

Answer 1

Hi @Garcia, Luis (NonEmp)
There are different ways that can be used to retrieve such data: NOT IN, NOT EXISTS and LEFT JOIN.
For me, I prefer NOT EXISTS. Unlike 'EXISTS and IN', 'NOT EXISTS and NOT IN' are not equivalent in all cases. Specifically, when NULLs are involved, they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.
Please refer to this similar thread: NOT IN vs NOT EXISTS

Regarding performance, you could refer to this article for more details: T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Your query is not very clear, if your query is

   SELECT ...  
   FROM  tbl  
   WHERE somecol NOT IN (SELECT item FROM Blacklist)  

That is about as good as it gets. It does help, though, if the Blacklist table is indexed with item as the primary key.