Hi Sergey,

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.