SQL中Execpt和notin性能区别

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

SELECT * FROM tb1 EXCEPT 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]))

SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);--得不到任何值
|--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 Loo(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))