SQL Server optimizes SQL statements in and not in

However, the SQL Performance with in is always relatively low. The SQL Execution steps are used to analyze the differences between SQL with in and SQL without in:
SQL tries to convert it to the join of multiple tables. If the conversion fails, it first executes the subquery in and then queries the outer table records, if the conversion is successful, multiple tables are directly connected for query. It can be seen that at least one conversion process is added to SQL statements using in. General SQL statements can be converted successfully, but SQL statements that contain grouping statistics cannot be converted. We recommend that you do not use the in operator in business-intensive SQL statements.
Not in this operation is not recommended for strong columns because it cannot apply table indexes. We recommend that you use the not exists or (Outer Join + null) scheme instead.
There are two tables in the database. One is the current table Info (ID, pname, remark, impdate, upstate), and the other is the backup data table bakinfo (ID, pname, remark, impdate, upstate ), backing up the data in the current table to the backup table involves the not in and in operations:
First, add 0.1 million pieces of test data CopyCode The Code is as follows: Create procedure adddata
As
Declare @ ID int
Set @ ID = 0
While (@ ID <100000)
Begin
Insert into DBO. Info (ID, pname, remark, impdate, upstate)
Values (@ ID, convert (varchar, @ ID) + '0', 'abc', getdate (), 0)
Set @ ID = @ ID + 1
End
Exec adddata

Use not in and in operations:Copy codeThe Code is as follows: Set statistics time on
Go
-- Backup data
Insert into bakinfo (ID, pname, remark, impdate, upstate)
Select ID, pname, remark, impdate, upstate from DBO. Info
Where id not in (select ID from DBO. bakinfo)
Go
Set statistics time off

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 3 ms.
SQL Server execution time:
CPU time = 453 milliseconds, occupied time = 43045 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.
-- Change the current table status
Update info set upstate = 1 where ID in (select ID from DBO. bakinfo)

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 62 ms, occupied time = 79 Ms.
SQL Server execution time:
CPU time = 188 milliseconds, occupied time = 318 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.
-- Delete data in the current table
Delete from info where upstate = 1 and ID in (select ID from DBO. bakinfo)

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 183 milliseconds, occupied time = 183 milliseconds.
SQL Server execution time:
CPU time = 187 milliseconds, occupied time = 1506 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.

Use join connection alternative solution:Copy codeThe Code is as follows: Set statistics time on
Go
-- Backup data
Insert into bakinfo (ID, pname, remark, impdate, upstate)
Select ID, pname, remark, impdate, upstate from
(Select info. ID, info. pname, info. remark, info. impdate, info. Upstate, bakinfo. ID as bakid
From info left join
Bakinfo on info. ID = bakinfo. ID) as t
Where T. bakid is null and T. Upstate = 0
Go
Set statistics time off;

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 247 milliseconds, occupied time = 247 milliseconds.
SQL Server execution time:
CPU time = 406 milliseconds, occupied time = 475 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.
-- Change the current table status
Update info set upstate = 1
From info inner join
Bakinfo on info. ID = bakinfo. ID

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 4 ms, occupied time = 4 ms.
SQL Server execution time:
CPU time = 219 milliseconds, occupied time = 259 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.

-- Delete data in the current tableCopy codeThe Code is as follows: delete from Info
From info inner join
Bakinfo on info. ID = bakinfo. ID
Where info. Upstate = 1

Execution time of this operation:Copy codeThe Code is as follows: SQL Server Analysis and Compilation Time:
CPU time = 177 milliseconds, occupied time = 177 milliseconds.
SQL Server execution time:
CPU time = 219 milliseconds, occupied time = 550 milliseconds.
(Row 3 is affected)
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 1 ms.

It can be seen that the join scheme is much shorter than the not in and in execution time.