Performance differences between SQL Server Execpt and not in

It mainly describes the performance differences between consumer T and not in.
Copy codeThe Code is as follows:
Create table tb1 (ID int)
Create table tb2 (ID int)
BEGIN TRAN
DECLARE @ I INT = 500
WHILE @ I> 0
Begin
Insert into dbo. tb1
VALUES (@ I -- v-int
)
SET @ I = @ I-1
End
COMMIT: tb1 is 1000, tb2 is 500

Copy codeThe Code is as follows:
Dbcc freesystemcache ('all', 'default ');
SET STATISTICS IO ON
Set statistics time on
SELECT * FROM tb1 into t select * FROM tb2;
SELECT * FROM tb1 WHERE id not in (SELECT id FROM tb2); -- no value is obtained.
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Execution Plan:
Copy codeThe Code is as follows:
SELECT * FROM tb1 into t select * FROM tb2;
| -- Merge Join (Right Anti Semi Join, MERGE :( [master1]. [dbo]. [tb2]. [ID]) = ([master1]. [dbo]. [tb1]. [ID]), RESIDUAL :( [master1]. [dbo]. [tb1]. [ID] = [master1]. [dbo]. [tb2]. [ID])
| -- Sort (distinct order by :( [master1]. [dbo]. [tb2]. [ID] ASC ))
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb2])
| -- Sort (distinct order by :( [master1]. [dbo]. [tb1]. [ID] ASC ))
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb1])

Copy codeThe Code is as follows:
SELECT * FROM tb1 WHERE id not in (SELECT id FROM tb2); -- no value is obtained.
| -- Hash Match (Right Anti Semi Join, HASH :( [master1]. [dbo]. [tb2]. [ID]) = ([master1]. [dbo]. [tb1]. [ID]), RESIDUAL :( [master1]. [dbo]. [tb1]. [ID] = [master1]. [dbo]. [tb2]. [ID])
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb2])
| -- Nested Loops (Left Anti Semi Join)
| -- Nested Loops (Left Anti Semi Join, WHERE :( [master1]. [dbo]. [tb1]. [ID] is null ))
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb1])
| -- Top (top expression :( (1 )))
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb2])
| -- Row Count Spool
| -- Table Scan (OBJECT :( [master1]. [dbo]. [tb2]), WHERE :( [master1]. [dbo]. [tb2]. [ID] is null ))

SQL Server execution time:
CPU time = 0 ms, occupied time = 0 ms.
(Row 3 is affected)
Table 'tab1 '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
Table 'tb2 '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
(6 rows affected)
(One row is affected)
SQL Server execution time:
CPU time = 0 ms, occupied time = 528 Ms.
(Row 3 is affected)
Table 'worktable '. Scan count 0, logical read 0, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
Table 'tb2 '. Scan count 3, logical reads 1002, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Table 'tab1 '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
(10 rows affected)
(One row is affected)
SQL Server execution time:
CPU time = 16 ms, occupied time = 498 Ms.
SQL Server execution time:
CPU time = 0 ms, occupied time = 0 ms.

Conclusion: through testing with a large amount of data and a small amount of data, the not in performance is better than that with a small amount of data, but execpt is superior to not in a large amount of data.
The execution plan shows how to create an index on tb1 and tb2. Then, the execution plan of ipvt can be optimized.

If you are interested, you can check the execution plan of not exists. Suggestion:
Do not trust the test results because all performance is closely related to the execution plan. The execution plan and statistical data are inseparable.
Therefore, excessive superstitious test results may affect the performance of the production database and fail to achieve the expected performance.