SQL - Want your query run faster?

Sometimes if you want to check if data in table A not exist in table B, but there is no data in the table B. If table A has large datasets, usually it takes very long time for the query to finish. Here is an alternative way to use not exists

set statistics time on 

select top 100000 a.*

from xxxxx.dbo.tbl_well_bridge a

     left join #Temp b  on a.api = b.api

where b.api is null;

select  top 100000 a.*

from xxxxxx.dbo.tbl_well_bridge a

where api not in (select api from #Temp)

select  top 100000  *

from xxxxx.dbo.tbl_well_bridge a

where not exists (select api from #Temp b where a.api = b.api )

set statistics time off

And the comparison is below. You can see clearly that which one is saving more time in total!

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 22396 ms.

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 63 ms,  elapsed time = 8155 ms.

(100000 rows affected)

 SQL Server Execution Times:

   CPU time = 78 ms,  elapsed time = 6028 ms.