Need an alternative to using a sub-query

Hi, I am trying to filter the data in my database using an SQL query, this works well with about 30,000 records but higher number of records results in time out, any idea on how I can optimize the query below without using the current sub-query?
Select * From myTable Where USER_ID = ‘USER_ABC’ and Final_References Not In(Select Final_References From myTable Group By Final_References HAVING COUNT(Final_References) > 1 and SUM(TXN_AMT) = 0);

Instead of using NOT IN, join on the subquery where Final_References = [sq].Final_References (I’m assuming alias [sq] for the subquery. Make this a LEFT JOIN, and in your final WHERE clause, filter WHERE [sq].Final_Reference IS NULL. This will pull all records where the join failed. Joins are also generally faster than using NOT IN.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.