Simple Analysis of exists and in SQL SERVER, serverexists

Simple Analysis of exists and in SQL SERVER, serverexists

The In and Exists functions are similar, but because of the different optimization schemes, NOT Exists Is usually faster than not in, because not exists can be used IN combination with algorithm 2 NOT In, EXISTS is not as fast as IN, because IN may use more combined algorithms at this time.

Now there are two datasets: # tempTable1 on the left and # tempTable2 on the right. There are the following problems:

1. What is the intersection of the two sets?

2. Check if tempTable1 does not belong to the set # The set of 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,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 use argument1 of # tempTable1 and # tempTable2 as a reference.

1. Calculate the intersection of the two sets:

1) in Method

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

2) exists Method

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

2. Find that tempTable1 does not belong to the set # tempTable2

1) in Method

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

2) exists Method

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

What are the differences between SQL statements IN and EXISTS?

IN fact, it is similar to equals. For example, in () is a simple method of writing = 1 or = 2, so IN is generally used when there are few elements. If there are many elements, exists is used.

The usage of exists is different from that of in. Generally, it needs to be associated with the sub-table. in addition, you need to use an index to accelerate the association.

Your SQL statement can be written as NOT EXISTS
Select MC001 from bommc where not exists (SELECT MD001 from bommd where BOMMC. MC001 = BOMMD. MD001)

In SQL is different from exists.

The main difference between in and exists is to process null.
If there is no null value, in and exists are equivalent. If they are not the same, the query content contains null values.
Please carefully analyze the following four examples.
Select 1
Where 2 in (1, null)

Select 1
Where 2 not in (1, null)

Select 1
Where exists (select null)

Select 1
Where not exists (select null)