>>>>>>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