SQL涓璄XISTS鐨勭敤娉
SQL涓璄XISTS鐨勭敤娉?
姣斿鍦∟orthwind鏁版嵁搴撲腑鏈変竴涓煡璇负
SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)
杩欓噷闈㈢殑EXISTS鏄浣曡繍浣滃憿锛熷瓙鏌ヨ杩斿洖鐨勬槸OrderId瀛楁锛屽彲鏄闈㈢殑鏌ヨ瑕佹壘鐨勬槸CustomerID鍜孋ompanyName瀛楁锛岃繖涓や釜瀛楁鑲畾涓嶅湪OrderID閲岄潰鍟婏紝杩欐槸濡備綍鍖归厤鐨勫憿锛?
EXISTS鐢ㄤ簬妫€鏌ュ瓙鏌ヨ鏄惁鑷冲皯浼氳繑鍥炰竴琛屾暟鎹紝璇ュ瓙鏌ヨ瀹為檯涓婂苟涓嶈繑鍥炰换浣曟暟鎹紝鑰屾槸杩斿洖鍊糡rue鎴朏alse
EXISTS 鎸囧畾涓€涓瓙鏌ヨ锛屾娴?琛?鐨勫瓨鍦ㄣ€?
璇硶锛?EXISTS subquery
鍙傛暟锛?subquery 鏄竴涓彈闄愮殑 SELECT 璇彞 (涓嶅厑璁告湁 COMPUTE 瀛愬彞鍜?INTO 鍏抽敭瀛?銆?
缁撴灉绫诲瀷锛?Boolean 濡傛灉瀛愭煡璇㈠寘鍚锛屽垯杩斿洖 TRUE 锛屽惁鍒欒繑鍥?FLASE 銆?
渚嬭〃A锛歍ableIn 渚嬭〃B锛歍ableEx
锛堜竴锛? 鍦ㄥ瓙鏌ヨ涓娇鐢?NULL 浠嶇劧杩斿洖缁撴灉闆?
select * from TableIn where exists(select null)
绛夊悓浜庯細 select * from TableIn
锛堜簩锛? 姣旇緝浣跨敤 EXISTS 鍜?IN 鐨勬煡璇€傛敞鎰忎袱涓煡璇㈣繑鍥炵浉鍚岀殑缁撴灉銆?
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)
锛堜笁锛? 姣旇緝浣跨敤 EXISTS 鍜?= ANY 鐨勬煡璇€傛敞鎰忎袱涓煡璇㈣繑鍥炵浉鍚岀殑缁撴灉銆?
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)
NOT EXISTS 鐨勪綔鐢ㄤ笌 EXISTS 姝ｅソ鐩稿弽銆傚鏋滃瓙鏌ヨ娌℃湁杩斿洖琛岋紝鍒欐弧瓒充簡 NOT EXISTS 涓殑 WHERE 瀛愬彞銆?
缁撹锛?
EXISTS(鍖呮嫭 NOT EXISTS )瀛愬彞鐨勮繑鍥炲€兼槸涓€涓狟OOL鍊笺€?EXISTS鍐呴儴鏈変竴涓瓙鏌ヨ璇彞(SELECT ... FROM...)锛?鎴戝皢鍏剁О涓篍XIST鐨勫唴鏌ヨ璇彞銆傚叾鍐呮煡璇㈣鍙ヨ繑鍥炰竴涓粨鏋滈泦銆?EXISTS瀛愬彞鏍规嵁鍏跺唴鏌ヨ璇彞鐨勭粨鏋滈泦绌烘垨鑰呴潪绌猴紝杩斿洖涓€涓竷灏斿€笺€?
涓€绉嶉€氫織鐨勫彲浠ョ悊瑙ｄ负锛氬皢澶栨煡璇㈣〃鐨勬瘡涓€琛岋紝浠ｅ叆鍐呮煡璇綔涓烘楠岋紝濡傛灉鍐呮煡璇㈣繑鍥炵殑缁撴灉鍙栭潪绌哄€硷紝鍒橢XISTS瀛愬彞杩斿洖TRUE锛岃繖涓€琛岃鍙綔涓哄鏌ヨ鐨勭粨鏋滆锛屽惁鍒欎笉鑳戒綔涓虹粨鏋溿€?
鍒嗘瀽鍣ㄤ細鍏堢湅璇彞鐨勭涓€涓瘝锛屽綋瀹冨彂鐜扮涓€涓瘝鏄疭ELECT鍏抽敭瀛楃殑鏃跺€欙紝瀹冧細璺冲埌FROM鍏抽敭瀛楋紝鐒跺悗閫氳繃FROM鍏抽敭瀛楁壘鍒拌〃鍚嶅苟鎶婅〃瑁呭叆鍐呭瓨銆傛帴鐫€鏄壘WHERE鍏抽敭瀛楋紝濡傛灉鎵句笉鍒板垯杩斿洖鍒癝ELECT鎵惧瓧娈佃В鏋愶紝濡傛灉鎵惧埌WHERE锛屽垯鍒嗘瀽鍏朵腑鐨勬潯浠讹紝瀹屾垚鍚庡啀鍥炲埌SELECT鍒嗘瀽瀛楁銆傛渶鍚庡舰鎴愪竴寮犳垜浠鐨勮櫄琛ㄣ€?
WHERE鍏抽敭瀛楀悗闈㈢殑鏄潯浠惰〃杈惧紡銆傛潯浠惰〃杈惧紡璁＄畻瀹屾垚鍚庯紝浼氭湁涓€涓繑鍥炲€硷紝鍗抽潪0鎴?锛岄潪0鍗充负鐪?true)锛?鍗充负鍋?false)銆傚悓鐞哤HERE鍚庨潰鐨勬潯浠朵篃鏈変竴涓繑鍥炲€硷紝鐪熸垨鍋囷紝鏉ョ‘瀹氭帴涓嬫潵鎵т笉鎵цSELECT銆?
鍒嗘瀽鍣ㄥ厛鎵惧埌鍏抽敭瀛桽ELECT锛岀劧鍚庤烦鍒癋ROM鍏抽敭瀛楀皢STUDENT琛ㄥ鍏ュ唴瀛橈紝骞堕€氳繃鎸囬拡鎵惧埌绗竴鏉¤褰曪紝鎺ョ潃鎵惧埌WHERE鍏抽敭瀛楄绠楀畠鐨勬潯浠惰〃杈惧紡锛屽鏋滀负鐪熼偅涔堟妸杩欐潯璁板綍瑁呭埌涓€涓櫄琛ㄥ綋涓紝鎸囬拡鍐嶆寚鍚戜笅涓€鏉¤褰曘€傚鏋滀负鍋囬偅涔堟寚閽堢洿鎺ユ寚鍚戜笅涓€鏉¤褰曪紝鑰屼笉杩涜鍏跺畠鎿嶄綔銆備竴鐩存绱㈠畬鏁翠釜琛紝骞舵妸妫€绱㈠嚭鏉ョ殑铏氭嫙琛ㄨ繑鍥炵粰鐢ㄦ埛銆侲XISTS鏄潯浠惰〃杈惧紡鐨勪竴閮ㄥ垎锛屽畠涔熸湁涓€涓繑鍥炲€?true鎴杅alse)銆?
鍦ㄦ彃鍏ヨ褰曞墠锛岄渶瑕佹鏌ヨ繖鏉¤褰曟槸鍚﹀凡缁忓瓨鍦紝鍙湁褰撹褰曚笉瀛樺湪鏃舵墠鎵ц鎻掑叆鎿嶄綔锛屽彲浠ラ€氳繃浣跨敤 EXISTS 鏉′欢鍙ラ槻姝㈡彃鍏ラ噸澶嶈褰曘€?
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 '寮犱笁', '鐢? FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)
EXISTS涓嶪N鐨勪娇鐢ㄦ晥鐜囩殑闂锛岄€氬父鎯呭喌涓嬮噰鐢╡xists瑕佹瘮in鏁堢巼楂橈紝鍥犱负IN涓嶈蛋绱㈠紩锛屼絾瑕佺湅瀹為檯鎯呭喌鍏蜂綋浣跨敤锛?
IN閫傚悎浜庡琛ㄥぇ鑰屽唴琛ㄥ皬鐨勬儏鍐碉紱EXISTS閫傚悎浜庡琛ㄥ皬鑰屽唴琛ㄥぇ鐨勬儏鍐点€?
濡傛灉涓や釜琛ㄤ腑涓€涓緝灏忥紝涓€涓槸澶ц〃锛屽垯瀛愭煡璇㈣〃澶х殑鐢╡xists锛屽瓙鏌ヨ琛ㄥ皬鐨勭敤in
姣斿鍦∟orthwind鏁版嵁搴撲腑鏈変竴涓煡璇负
SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)
杩欓噷闈㈢殑EXISTS鏄浣曡繍浣滃憿锛熷瓙鏌ヨ杩斿洖鐨勬槸OrderId瀛楁锛屽彲鏄闈㈢殑鏌ヨ瑕佹壘鐨勬槸CustomerID鍜孋ompanyName瀛楁锛岃繖涓や釜瀛楁鑲畾涓嶅湪OrderID閲岄潰鍟婏紝杩欐槸濡備綍鍖归厤鐨勫憿锛?
EXISTS鐢ㄤ簬妫€鏌ュ瓙鏌ヨ鏄惁鑷冲皯浼氳繑鍥炰竴琛屾暟鎹紝璇ュ瓙鏌ヨ瀹為檯涓婂苟涓嶈繑鍥炰换浣曟暟鎹紝鑰屾槸杩斿洖鍊糡rue鎴朏alse
EXISTS 鎸囧畾涓€涓瓙鏌ヨ锛屾娴?琛?鐨勫瓨鍦ㄣ€?
璇硶锛?EXISTS subquery
鍙傛暟锛?subquery 鏄竴涓彈闄愮殑 SELECT 璇彞 (涓嶅厑璁告湁 COMPUTE 瀛愬彞鍜?INTO 鍏抽敭瀛?銆?
缁撴灉绫诲瀷锛?Boolean 濡傛灉瀛愭煡璇㈠寘鍚锛屽垯杩斿洖 TRUE 锛屽惁鍒欒繑鍥?FLASE 銆?
渚嬭〃A锛歍ableIn 渚嬭〃B锛歍ableEx
锛堜竴锛? 鍦ㄥ瓙鏌ヨ涓娇鐢?NULL 浠嶇劧杩斿洖缁撴灉闆?
select * from TableIn where exists(select null)
绛夊悓浜庯細 select * from TableIn
锛堜簩锛? 姣旇緝浣跨敤 EXISTS 鍜?IN 鐨勬煡璇€傛敞鎰忎袱涓煡璇㈣繑鍥炵浉鍚岀殑缁撴灉銆?
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)
锛堜笁锛? 姣旇緝浣跨敤 EXISTS 鍜?= ANY 鐨勬煡璇€傛敞鎰忎袱涓煡璇㈣繑鍥炵浉鍚岀殑缁撴灉銆?
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)
NOT EXISTS 鐨勪綔鐢ㄤ笌 EXISTS 姝ｅソ鐩稿弽銆傚鏋滃瓙鏌ヨ娌℃湁杩斿洖琛岋紝鍒欐弧瓒充簡 NOT EXISTS 涓殑 WHERE 瀛愬彞銆?
缁撹锛?
EXISTS(鍖呮嫭 NOT EXISTS )瀛愬彞鐨勮繑鍥炲€兼槸涓€涓狟OOL鍊笺€?EXISTS鍐呴儴鏈変竴涓瓙鏌ヨ璇彞(SELECT ... FROM...)锛?鎴戝皢鍏剁О涓篍XIST鐨勫唴鏌ヨ璇彞銆傚叾鍐呮煡璇㈣鍙ヨ繑鍥炰竴涓粨鏋滈泦銆?EXISTS瀛愬彞鏍规嵁鍏跺唴鏌ヨ璇彞鐨勭粨鏋滈泦绌烘垨鑰呴潪绌猴紝杩斿洖涓€涓竷灏斿€笺€?
涓€绉嶉€氫織鐨勫彲浠ョ悊瑙ｄ负锛氬皢澶栨煡璇㈣〃鐨勬瘡涓€琛岋紝浠ｅ叆鍐呮煡璇綔涓烘楠岋紝濡傛灉鍐呮煡璇㈣繑鍥炵殑缁撴灉鍙栭潪绌哄€硷紝鍒橢XISTS瀛愬彞杩斿洖TRUE锛岃繖涓€琛岃鍙綔涓哄鏌ヨ鐨勭粨鏋滆锛屽惁鍒欎笉鑳戒綔涓虹粨鏋溿€?
鍒嗘瀽鍣ㄤ細鍏堢湅璇彞鐨勭涓€涓瘝锛屽綋瀹冨彂鐜扮涓€涓瘝鏄疭ELECT鍏抽敭瀛楃殑鏃跺€欙紝瀹冧細璺冲埌FROM鍏抽敭瀛楋紝鐒跺悗閫氳繃FROM鍏抽敭瀛楁壘鍒拌〃鍚嶅苟鎶婅〃瑁呭叆鍐呭瓨銆傛帴鐫€鏄壘WHERE鍏抽敭瀛楋紝濡傛灉鎵句笉鍒板垯杩斿洖鍒癝ELECT鎵惧瓧娈佃В鏋愶紝濡傛灉鎵惧埌WHERE锛屽垯鍒嗘瀽鍏朵腑鐨勬潯浠讹紝瀹屾垚鍚庡啀鍥炲埌SELECT鍒嗘瀽瀛楁銆傛渶鍚庡舰鎴愪竴寮犳垜浠鐨勮櫄琛ㄣ€?
WHERE鍏抽敭瀛楀悗闈㈢殑鏄潯浠惰〃杈惧紡銆傛潯浠惰〃杈惧紡璁＄畻瀹屾垚鍚庯紝浼氭湁涓€涓繑鍥炲€硷紝鍗抽潪0鎴?锛岄潪0鍗充负鐪?true)锛?鍗充负鍋?false)銆傚悓鐞哤HERE鍚庨潰鐨勬潯浠朵篃鏈変竴涓繑鍥炲€硷紝鐪熸垨鍋囷紝鏉ョ‘瀹氭帴涓嬫潵鎵т笉鎵цSELECT銆?
鍒嗘瀽鍣ㄥ厛鎵惧埌鍏抽敭瀛桽ELECT锛岀劧鍚庤烦鍒癋ROM鍏抽敭瀛楀皢STUDENT琛ㄥ鍏ュ唴瀛橈紝骞堕€氳繃鎸囬拡鎵惧埌绗竴鏉¤褰曪紝鎺ョ潃鎵惧埌WHERE鍏抽敭瀛楄绠楀畠鐨勬潯浠惰〃杈惧紡锛屽鏋滀负鐪熼偅涔堟妸杩欐潯璁板綍瑁呭埌涓€涓櫄琛ㄥ綋涓紝鎸囬拡鍐嶆寚鍚戜笅涓€鏉¤褰曘€傚鏋滀负鍋囬偅涔堟寚閽堢洿鎺ユ寚鍚戜笅涓€鏉¤褰曪紝鑰屼笉杩涜鍏跺畠鎿嶄綔銆備竴鐩存绱㈠畬鏁翠釜琛紝骞舵妸妫€绱㈠嚭鏉ョ殑铏氭嫙琛ㄨ繑鍥炵粰鐢ㄦ埛銆侲XISTS鏄潯浠惰〃杈惧紡鐨勪竴閮ㄥ垎锛屽畠涔熸湁涓€涓繑鍥炲€?true鎴杅alse)銆?
鍦ㄦ彃鍏ヨ褰曞墠锛岄渶瑕佹鏌ヨ繖鏉¤褰曟槸鍚﹀凡缁忓瓨鍦紝鍙湁褰撹褰曚笉瀛樺湪鏃舵墠鎵ц鎻掑叆鎿嶄綔锛屽彲浠ラ€氳繃浣跨敤 EXISTS 鏉′欢鍙ラ槻姝㈡彃鍏ラ噸澶嶈褰曘€?
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 '寮犱笁', '鐢? FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)
EXISTS涓嶪N鐨勪娇鐢ㄦ晥鐜囩殑闂锛岄€氬父鎯呭喌涓嬮噰鐢╡xists瑕佹瘮in鏁堢巼楂橈紝鍥犱负IN涓嶈蛋绱㈠紩锛屼絾瑕佺湅瀹為檯鎯呭喌鍏蜂綋浣跨敤锛?
IN閫傚悎浜庡琛ㄥぇ鑰屽唴琛ㄥ皬鐨勬儏鍐碉紱EXISTS閫傚悎浜庡琛ㄥ皬鑰屽唴琛ㄥぇ鐨勬儏鍐点€?
濡傛灉涓や釜琛ㄤ腑涓€涓緝灏忥紝涓€涓槸澶ц〃锛屽垯瀛愭煡璇㈣〃澶х殑鐢╡xists锛屽瓙鏌ヨ琛ㄥ皬鐨勭敤in