姣斿?鍦∟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