msi.table_aa ID NAME
1 A1
2 A2
3 A3msi.table_bb ID AID NAME
1 1 B1
2 2 B2
3 2 B3 SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b
WHERE B.AID = A.ID)结果:NAME:A1,A2
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID)结果:NAME:A1,A2,A3这个现象谁可以解释下?在线等!
1 A1
2 A2
3 A3msi.table_bb ID AID NAME
1 1 B1
2 2 B2
3 2 B3 SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b
WHERE B.AID = A.ID)结果:NAME:A1,A2
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID)结果:NAME:A1,A2,A3这个现象谁可以解释下?在线等!
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID)B.AID = A.ID
这个A应该是你msi.table_aa A这个,所以全有
你可以改成msi.table_aa A1,,试试
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A1
WHERE B.AID = A.ID)
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID结果是:NAME:A1,A2
为什么外层的是:
NAME:A1,A2,A3??????
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID)如果加上这段 msi.table_aa A 就相当于这个exists没有用。就等于SELECT A.NAME
FROM msi.table_aa A
ID NAME
1 A1
2 A2
3 A3
msi.table_bb b
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
————————————————————————————————————————————————————SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT A.NAME
FROM msi.table_bb b
WHERE B.AID = A.ID)
首先解釋下,這裡面有個說法稱作:谓词Exists相关子查询。執行的過程是:先取msi.table_aa A第一個元組,然後再根據和內層查詢查詢相關屬性B.AID = A.ID處理內層,諾where 子句返回true,则取
此远组放入结果表中,然后进入下一个元组,重複這一過程,直到外部表全部檢查完畢。
應為:msi.table_aa A,msi.table_bb b 通過ID連接,msi.table_bb 中只有1,2。
所以結果只有NAME:A1,A2。
結果:NAME:A1,A2
————————————————————————————————————————————————————
SELECT A.NAME
FROM msi.table_aa A
WHERE EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID)結果:NAME:A1,A2,A3
附:SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID
結果:NAME:A1,A2
這裡沒有Exists相关子查询的概念,因為內層查詢不涉及到外層的表。這裡EXISTS (SELECT distinct A.NAME
FROM msi.table_bb b, msi.table_aa A
WHERE B.AID = A.ID) 返回的是個true;
所以它返回的是NAME:A1,A2,A3一点总结如上!