表A
ID NAME
1 A1
2 A2
3 A3表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
表A和表B是1对多的关系 A.ID => B.AIDSELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
ID NAME
1 A1
2 A2
3 A3表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
表A和表B是1对多的关系 A.ID => B.AIDSELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
create table A
(
ID int,
[Name] varchar(10)
)Create table B
(
ID int,
AID int,
[Name] varchar(10)
)
insert into A
select 1 , 'A1' union all
select 2 , 'A2' union all
select 3, 'A3'
insert into B
select 1 , 1, 'A1' union all
select 2 , 2, 'A2' union all
select 3, 2, 'A3' SELECT ID,[NAME] FROM A WHERE EXISTs (SELECT * FROM B WHERE A.ID=B.AID) 尝试了一下检查它的运行plan
应该是先扫描A,B表发现A.ID=B.AID
然后是一个nest loop(left semi join)怀疑是用了半join连接
测试下面这句SELECT distinct A.ID,A.[NAME] FROM B left join A on A.ID=B.AID
执行计划基本上是一样的,只是最后加了一个distinct sort所以觉得EXISTs 语句如果又传递数据到子查询里面,一定程度上等价于一个左链接的判断
go
SQL語句
go
SET SHOWPLAN_all off;