I run the same test using actual table and got following results:
1 time
NOT IN() : 436ms
AND : 390ms
OUTER JOIN: 360ms
NOT EXISTS: 343ms
10 times
NOT IN() : 3,936ms
AND : 3,923ms
OUTER JOIN: 3,936ms
NOT EXISTS: 3,873ms
100 times
NOT IN() : 39,530ms
AND : 39,360ms
OUTER JOIN: 41,516ms
NOT EXISTS: 42,076ms
It still seems that the first 2 methods are the fastest.
Do we have to think differently when using SQL 2000?
Anyway, I think it is very interesting.
K. Matsumura