Delete from #T1 Where exists(Select Id From #T2 where #T2.Id=#T1.Id)
I suggest Using
exists
Instead of
In
because
EXISTS
will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing
Tested:
Create table #T1 (Id int)
Create table #T2 (Id int)
Insert into #T1
Select 1 Union all
Select 2 Union all
Select 3 Union all
Select 4 Union all
Select 5
Insert into #T2
Select 1 Union all
Select 2 Union all
Select 3
Delete from #T1 Where exists(Select Id From #T2 where #T2.Id=#T1.Id)
Select * From #T1 -- Returns 4,5
Drop Table #T1
drop Table #T2
http://www.techonthenet.com/sql/delete.php[
^]