Usually NOT EXISTS subquery performs slightly faster than NOT IN.

In older versions of SQL Server (i.e. prior to SQL Server 2005), EXISTS/NOT EXISTS was indeed usually faster than IN/NOT IN.

But Microsoft made some changes under the hood, which has led some people to assume that they now perform the same.

Here's my understanding....it depends on the context of NULL values (this has been talked about in many posts on sqlservercentral.com).

If the column being used permits NULL values but no NULL values exists, NOT EXISTS will perform faster (sometimes several times faster) than NOT IN.

If that's not the case, my understanding is that the two are usually going to perform about the same.

Admittedly, I'll use IN/NOT IN when dealing with one column and EXISTS/NOT EXISTS if I have to evaluate multiple columns.