>>>>>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.
If it's not broken, fix it until it is.
My Blog