34,575
社区成员
发帖
与我相关
我的任务
分享
set nocount on
create table a(id int);
create table b(id int);
go
insert into A values(RAND(checksum(newid()))*100);
go 1000
insert into B values(RAND(checksum(newid()))*100);
go 10
set showplan_text on
select * from b
where id not in (select id from a)
select * from b
where not exists(Select 1 from a where a.id = b.id)
--not in(cost 70%)
| |--Nested Loops(Left Anti Semi Join, WHERE:([master].[dbo].[b].[id] IS NULL))
| | |--Table Scan(OBJECT:([master].[dbo].[b]))
| | |--Top(TOP EXPRESSION:((1)))
| | |--Table Scan(OBJECT:([master].[dbo].[a]))
| |--Row Count Spool
| |--Table Scan(OBJECT:([master].[dbo].[a]), WHERE:([master].[dbo].[a].[id] IS NULL))
|--Top(TOP EXPRESSION:((1)))
|--Table Scan(OBJECT:([master].[dbo].[a]), WHERE:([master].[dbo].[b].[id]=[master].[dbo].[a].[id]))
-- not exists (cost 30%)
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([master].[dbo].[b].[id]))
|--Table Scan(OBJECT:([master].[dbo].[b]))
|--Top(TOP EXPRESSION:((1)))
|--Table Scan(OBJECT:([master].[dbo].[a]), WHERE:([master].[dbo].[a].[id]=[master].[dbo].[b].[id]))
select * from b except select * from a