As I said, only NULLs could be responsible for such difference
select EventsLog.PK
from TVM.dbo.EventsLog
where EventsLog.PK not in (select FaultsLog.fkEventsLog from tvm.dbo.FaultsLog WHERE fkEventsLog IS NOT NULL)
>Well, I'll be darned.
>
>
>select EventsLog.PK
> from TVM.dbo.EventsLog
> where EventsLog.PK not in (select FaultsLog.fkEventsLog from tvm.dbo.FaultsLog) && Returns 0 Records
>
>
>select EventsLog.PK
> from TVM.dbo.EventsLog
> where not exists (select 1 from tvm.dbo.FaultsLog WHERE EventsLog.PK = FaultsLog.fkEventsLog) && Returns 639708 Records
>
>
>
>I would have sweared that aside from performance issues both queries would return the same data set? Obviously I was wrong, yet I do not understand why.
>
>Anyways, I think that a night's rest (although I could barely sleep...) helped finding the problem. My original query is on a trigger and it uses a different exists clause, to check that there is not an open fault for the same event (for example if the event if "power off", which is a fault then it writes in the faults log, but if there is another "power off" event without a "power on" event in between, then I do not want to write it again). The thing is that I just noticed that in the condition I forgot to add one field, which hopefully will solve the problem.
>
>In my stupidity, yesterday I took the trigger code and tried to run it from outside, then I added the "not in" condition (as I did not want records that the trigger had processed correctly) and as I was getting no records there, I panicked (to tell you the truth, I am still worried as I do not understand why both selects yield different results) and thought the problem was there.
>
>Well, sorry for all the chatter and thanks for your help, any idea why this difference? In my case I will not use "not in" ever again.
>
>>Hi Hugo,
>>
>>Either your simplification is incorrect or NULLs are involved somehow.
--sb--