>>>>>>>Yes, it should be a LEFT JOIN (was a typo, I assume). Check this link http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#4
>>>>>>
>>>>>>
>>>>>>bu-bu-but i like number 3 and number 1 must i use number 2?
>>>>>
>>>>>No. Unless your field accepts null, you don't have to do that.
>>>>>Cetin
>>>>
>>>>Are you saying it's a common misconception that LEFT JOIN is the fastest method?
>>>
>>>No I didn't say that. For "this task" I don't think someone said it was the fastest method (I heard it from you). Think of it, it first does what "in" or "Exists" would do and then filter the result.
>>>Cetin
>>
>>Sergey said it as well.
>>
>>Anyway, here are my results
>>
>>

>>SQL Server Execution Times:
>>   CPU time = 219 ms,  elapsed time = 968 ms. -- NOT IN
>>
>> SQL Server Execution Times:
>>   CPU time = 125 ms,  elapsed time = 365 ms. -- NOT EXISTS
>>
>> SQL Server Execution Times:
>>   CPU time = 171 ms,  elapsed time = 268 ms. -- LEFT JOIN
>>

>>
>>Second try:
>>
>>

>>SQL Server Execution Times:
>>   CPU time = 156 ms,  elapsed time = 337 ms.
>>
>> SQL Server Execution Times:
>>   CPU time = 141 ms,  elapsed time = 365 ms.
>>
>> SQL Server Execution Times:
>>   CPU time = 187 ms,  elapsed time = 277 ms.

>>
>>
>>The execution plan was different and it took 39% vs. 31 for first two cases, but as you see the actual elapsed time is less for the LEFT JOIN.
>>
>>I ran the test on 500K Bid table and 100K Items table.
>
>Try with at least 2 million records each. For tables like this I wouldn't even waste my time for testing. What is the importance 365 ms vs 277 ms? Just less than 90 ms.
>Cetin

I'll try later - busy with work right now. I tried on the tables I had in my test database (in SQL Server).