Simple analysis in SQL Server about exists and in _mssql

In and exists these two functions are similar, but because the optimization scheme is different, the not exists is usually faster than not, because the not exists can use the binding algorithm two not in, and the exists is not as fast as in, Because at this time in may use the binding algorithm more.

As shown, there are now two datasets, the left represents #temptable1, and the right side represents #temptable2. The following questions are now available:

1. Find the intersection of two sets?

2. Find the set of TempTable1 not belong to set #temptable2?

First create two temporary tables:

CREATE table #tempTable1 (argument1 nvarchar (), argument2 varchar (), Argument3 datetime, Argument4 int); Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher001 ', ' 13023218757 ', GETDATE () -1,1
);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher002 ', ' 23218757 ', GETDATE () -2,2);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher003 ', ' 13018757 ', GETDATE () -3,3);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher004 ', ' 13023257 ', GETDATE () -4,4);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher005 ', ' 13023218 ', GETDATE () -5,5);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher006 ', ' 13023218 ', GETDATE () -6,6);
Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher007 ', ' 13023218 ', GETDATE () -7,7); Insert into #tempTable1 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher008 ',' 13023218 ', GETDATE () -8,8);
CREATE table #tempTable2 (argument1 nvarchar (), argument2 varchar (), Argument3 datetime, Argument4 int); Insert into #tempTable2 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher001 ', ' 13023218757 ', GETDATE () -1,1
); Insert into #tempTable2 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher0010 ', ' 23218757 ', GETDATE () -10,10
);
Insert into #tempTable2 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher003 ', ' 13018757 ', GETDATE () -3,3);
Insert into #tempTable2 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher004 ', ' 13023257 ', GETDATE () -4,4);

 Insert into #tempTable2 (ARGUMENT1,ARGUMENT2,ARGUMENT3,ARGUMENT4) VALUES (' preacher009 ', ' 13023218 ', GETDATE () -9,9);

For example, I now refer to the argument1 of #temptable1 and #temptable2.

1. Find the intersection of two sets:

1) in mode

SELECT * from #tempTable2 where argument1 in
(select Argument1 from #tempTable1)

2) Exists way

SELECT * from #tempTable2 T2 where exists 
(SELECT * from #tempTable1 t1 where t1.argument1=t2.argument1)

2. Find the set of TempTable1 not belong to set #temptable2

1) in mode

SELECT * from #tempTable1 where argument1 isn't in
(select Argument1 from #tempTable2)

2) Exists way

SELECT * from #tempTable1 t1 where not exists 
(SELECT * from #tempTable2 T2 where t1.argument1=t2.argument1)