2

I am trying to run a simple query but it is not returning any results and is showing "Executing Query" endlessly.

This is my code:

select *
from ALERTTRAN_01
where PARENT_ALERT_ID not in (select PARENT_ALERT_ID
                              from nonbulkclosealert)

But when I change the code to

select *
from ALERTTRAN_01
where PARENT_ALERT_ID in (select PARENT_ALERT_ID
                          from nonbulkclosealert)

Note the not in to in, then it is running fine.

Any help is appreciated.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Chan YL
  • 23
  • 2
  • 3
    `Not in` probably returns a lot more results. How many rows in the table? How many do you expect to be returned? What indexes do you have? – Dale K Oct 28 '21 at 03:08
  • 3
    Yes execution plan would help too, schema (e.g. is PARENT_ALERT_ID nullable in either table, do the data types match exactly), and whether you tried a NOT EXISTS pattern instead of NOT IN/ – Aaron Bertrand Oct 28 '21 at 03:13
  • We need to see table and index definitions, and a query plan. You can share query plans via https://brentozar.com/pastetheplan. Perhaps change it to `not exists`, see https://stackoverflow.com/questions/173041/not-in-vs-not-exists – Charlieface Oct 28 '21 at 10:28

1 Answers1

1

The performance with IN and NOT IN can be a little tricky, because then initially loads all the data within the sub query to the memory before it matches the records. So if there are more rows, it'll drain the performance. So try with EXISTS and NOT EXISTS instead, like this

select *
from ALERTTRAN_01
where not EXISTS (
    select PARENT_ALERT_ID
    from nonbulkclosealert WHERE PARENT_ALERT_ID = ALERTTRAN_01.PARENT_ALERT_ID 
)

Here are a few helpful links that'll explain the advantages of EXISTS over IN

https://www.sqlservercentral.com/blogs/not-exists-vs-not-in

https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • 1
    The difference between them is nothing to do with performance, and everything to do with semantics: `NOT IN` has different null semantics than `NOT EXISTS`. – Charlieface Oct 28 '21 at 11:36