set nocount on
create table a(id int);
create table b(id int);
go
insert into A values(RAND(checksum(newid()))*100);
go 1000insert into B values(RAND(checksum(newid()))*100);
go 10set 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]))
为啥not exists会有这么蛋疼的执行计划呢。 我本来一直以为它们俩是一样的呢(除了对null的处理不同)。
create table a(id int);
create table b(id int);
go
insert into A values(RAND(checksum(newid()))*100);
go 1000insert into B values(RAND(checksum(newid()))*100);
go 10set 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]))
为啥not exists会有这么蛋疼的执行计划呢。 我本来一直以为它们俩是一样的呢(除了对null的处理不同)。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货