Shulou(Shulou.com)05/31 Report--

This article introduces you how to carry on the simple analysis of exists and in in SQL SERVER, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The two functions of In and Exists are similar, but due to different optimization schemes, NOT Exists is usually faster than NOT IN, because NOT EXISTS can use the combination algorithm not IN, while EXISTS is not as fast as IN, because at this time IN may use the combination algorithm more often.

As shown in the figure, there are now two datasets, with # tempTable1 on the left and # tempTable2 on the right. Now there are the following problems:

1. Find the intersection of two sets?

two。 Find a set in tempTable1 that does not belong to set # tempTable2?

Create two temporary tables first:

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 preacher001','13023218757',GETDATE 1); insert into # tempTable1 (argument1,argument2,argument3,argument4) values (' preacher002','23218757',GETDATE ()-2); insert into # tempTable1 (argument1,argument2,argument3,argument4) values ('preacher003','13018757',GETDATE ()-3p3) Insert into # tempTable1 (argument1,argument2,argument3,argument4) values ('preacher004','13023257',GETDATE ()-4Power4); insert into # tempTable1 (argument1,argument2,argument3,argument4) values (' preacher005','13023218',GETDATE ()-5Power5); insert into # tempTable1 (argument1,argument2,argument3,argument4) values ('preacher006','13023218',GETDATE ()-6pc6); insert into # tempTable1 (argument1,argument2,argument3,argument4) values (' preacher007','13023218',GETDATE ()-7d7) 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); insert into # tempTable2 (argument1,argument2,argument3,argument4) values ('preacher0010','23218757',GETDATE ()-10) Insert into # tempTable2 (argument1,argument2,argument3,argument4) values ('preacher003','13018757',GETDATE ()-3 preacher004','13023257',GETDATE 3); insert into # tempTable2 (argument1,argument2,argument3,argument4) values (' preacher004','13023257',GETDATE ()-4 Magazine 4); insert into # tempTable2 (argument1,argument2,argument3,argument4) values ('preacher009','13023218',GETDATE ()-9 Magi 9)

For example, I now use the argument1 of # tempTable1 and # tempTable2 as a reference

1. Find the intersection of two episodes:

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)

two。 Find the sets in tempTable1 that do 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) is here to share the simple analysis of exists and in in SQL SERVER. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.