Simple analysis of exists and in in SQL Server

Time:2021-9-17

The in and exists functions are similar. However, due to different optimization schemes, not exists is usually faster than not in, because not exists can use the combination algorithm. Second, not in cannot, while exists is not as fast as in, because in may use more combination algorithms at this time.

As shown in the figure, there are two data sets. The left represents #temptable1 and the right represents #temptable2. Now there are the following questions:

1. Find the intersection of two sets?

2. Find the set in temptable1 that does not belong to set #temptable2?

First create two temporary tables:


create table #tempTable1
(
  argument1 nvarchar(50),
  argument2 varchar(20),
  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(50),
  argument2 varchar(20),
  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 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 mode


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

2. Find the set in temptable1 that does not belong to set #temptable2

1) In mode


select * from #tempTable1 where argument1 not in
(select argument1 from #tempTable2)

2) Exists mode


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