SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据
不懂
表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.AID SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=2有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=3无值返回真所以没有数据
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据
不懂
表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.AID SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=2有值返回真所以有数据 SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=3无值返回真所以没有数据
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据 不过这句实在太牵强.不如:SELECT distinct a.ID,a.NAME FROM A , b where a.id = B.AID
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据 ===SELECT ID,NAME FROM A WHERE true
不就是选择全部吗》??
-->
SELECT distinct a.ID,a.NAME FROM A , b where a.id = B.AID 因为使用exists , in , not in , not exists的子查询,查询速度将会变慢.
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 语句如果又传递数据到子查询里面,一定程度上等价于一个左链接的判断