not in、in 优化

declare @a as table
(
  id int,
  name varchar(10)
)
insert into @a(id,name)
values
(1,'A'),
(1,'A+'),
(2,'AA'),
(3,'AAA'),
(4,'AAAA'),
(5,'AAAAA')

declare @b as table
(
  id int,
  name varchar(10),
  pid int
)
insert into @b(id,name,pid)
values
(1,'1B',1),
(3,'3B',3),
(5,'5B',5),
(5,'5B+',5),
(7,'7B',7),
(9,'9B',9)

select * from @a

select * from @b 

 

 

select * 
from @a as a
where id not in (
select pid from @b
)

select *
from @a as a
where not exists (
select 1 from @b as b 
where b.pid=a.id
)

select a.*
from @a as a
left join @b as b on b.pid=a.id
where b.pid is null

 

 

  

select a.* 
from @a as a
left join @b as b on b.pid=a.id

 

select a.*, ' --- ' as leftjoin, b.* 
from @a as a
left join @b as b on b.pid=a.id 

 

select a.*,' --- ' as leftjoin,b.* 
from @a as a
left join @b as b on b.pid=a.id
where b.pid is null

 

 

select a.*, ' --- ' as leftjoin, b.* 
from @a as a
left join @b as b on b.pid=a.id
where b.pid is not null

 

 

In 

select a.*
from @a as a
where a.id in (
select pid from @b
)

 

select *
from @a as a
where exists (
select 1 from @b as b where b.pid=a.id
)

 

select a.*
from @a as a
inner join @b as b on b.pid=a.id

 

select a.*, b.*
from @a as a
inner join @b as b on b.pid=a.id