I run same test against 1,000,000 records and got following results:

1 time

NOT IN() : 390ms

AND : 373ms

OUTER JOIN: 1,876ms

NOT EXISTS: 966ms

10 times

NOT IN() : 3,826ms

AND : 3,826ms

OUTER JOIN: 18,643ms

NOT EXISTS: 9,593ms

100 times

NOT IN() : 38,203ms

AND : 38,250ms

OUTER JOIN: 184,656ms

NOT EXISTS: 95,703ms

I used SQL 2000 and table variable.

It seems that the first 2 methods are the fastest.

Is everyone using SQL 2K5?

I will run the same test with actual table to see if there is any difference.

K. Matsumura