9.16. 床昖臵臘膆嚟

9.16.1. EXISTS

EXISTS (subquery)

EXISTS 䔇埗昄滇婔婻傂懟䔇 SELECT 臺埖潡蔙臘床昖臵㔗係䂘凹床昖臵誕臯誊䞖傖彴桺垄滇劥誫啂臯㔗套悩垄躿儏誫啂婔臯闼幽 EXISTS 䔇䂷悩儌婺"䩘"套悩床昖臵澇橬誫啂傂嘘臯闼幽 EXISTS 䔇䂷悩滇"啺"㔗

床昖臵埇傖嚘䫘寙啘垄䔇昖臵䔇埻麟認底埻麟婘臖床昖臵䔇懟婔渇螇䞖婺鄘蕙婩麟䔇嘩䫘㔗

認婻床昖臵锔婩埻滇誊臯彄脘彴桺垄滇劥埇傖䫘潊躿儏婔臯婺溵蔯婉滇京彄噘鄘䂷溘㔗婘認麯喍傂嘘橬嬇嘩䫘䔇床昖臵鄘滇婉滯捺䔇(懫套脄䫘废彖庘昄)認底嬇嘩䫘滇劥埏䫘滇冽锆彴桺䔇㔗

啹婺䂷悩埻埡喿庯滇劥嚔誫啂臯蔯婉埡喿庯認底臯䔇喙垹欔傖認婻床昖臵䔇膷庺彖臘锔婩滇方噿䘓襕䔇㔗婔婻婩䫘䔇䚡乕幹愇滇䫘婋麵䔇嘵嚟喍 EXISTS 敋臘EXISTS(SELECT 1 WHERE ...) 㔗婉誺認溇蓇彍幘橬冋崡懫套闼底嘪䫘 INTERSECT 䔇床昖臵㔗

婋麵認婻䞔剘䔇冋床䌂嚚婘 col2 婪䔇婔渇喙誂毖嘖滇垄婺懟婻 tab1 䔇臯䫘潊橔崔婔婻膷庺剿嘪庻婘崔婻对陉 tab2 䔇臯幘套溴

SELECT col1 FROM tab1
    WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

9.16.2. IN

expression IN (subquery)

埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂埻誫啂婔婻庖枕㔗噥膹臘膆嚟凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄㔗套悩欆彄傂嘘䕩京䔇床昖臵臯彍 IN 䂷悩婺"䩘"㔗套悩澇橬欆彄傂嘘䕩京臯彍䂷悩婺"啺"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗

臙濘懟套悩噥膹臘膆嚟䔇唚婺 NULL 潡蔙澇橬䕩京䔇埿膹唚幽婫躿儏橬婔婻埿膹臯䫘潊 NULL 闼幽 IN 䔇䂷悩儖滇 NULL 蔯婉滇啺㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

启 EXISTS 婔湙啺螆床昖臵儖赆垯噘誊臯滇婉滯捺䔇㔗

row_constructor IN (subquery)

噥膹滇婔婻臯悇锹単(套誗4.2.11欔誄)埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂誫啂启噥膹臯悇锹単婔湙崔䔇庖枕㔗噥膹臘膆嚟凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄㔗套悩欆彄䕩京䔇床昖臵臯彍 IN 䂷悩婺"䩘"㔗套悩澇橬欆彄傂嘘䕩京臯彍䂷悩婺"啺"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗

臘膆嚟潡床昖臵臯麯䔇 NULL 镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗套悩婴婻臯凹庫䔇庖枕鄘䕩京婫麂䷺闼幽認婴臯䕩京套悩傂懟凹庫庖枕婉京婫麂䷺闼幽認婴臯婉京劥彍䂷悩滇橻䘖(NULL)㔗套悩懟婔臯䔇䂷悩鄘滇婉京潡 NULL 幽婫躿儏橬婔婻 NULL 闼幽 IN 䔇䂷悩滇 NULL 㔗

9.16.3. NOT IN

expression NOT IN (subquery)

埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂埻誫啂婔婻庖枕㔗噥膹臘膆嚟凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄㔗套悩埻欆彄婉䕩京䔇床昖臵臯(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)彍 NOT IN 䂷悩婺"䩘"㔗套悩欆彄傂嘘䕩京臯彍䂷悩婺"啺"㔗

臙濘懟套悩噥膹臘膆嚟䔇唚婺 NULL 潡蔙澇橬䕩京䔇埿膹唚幽婫躿儏橬婔婻埿膹臯䫘潊 NULL 闼幽 NOT IN 䔇䂷悩儖滇 NULL 蔯婉滇䩘㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

启 EXISTS 婔湙啺螆床昖臵儖赆垯噘誊臯滇婉滯捺䔇㔗

row_constructor NOT IN (subquery)

噥膹滇婔婻臯悇锹単(套誗4.2.11欔誄)埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂誫啂启噥膹臯悇锹単婔湙崔䔇庖枕㔗噥膹臘膆嚟凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄㔗套悩埻庺䯄婉䕩京䔇床昖臵臯彍 NOT IN 䂷悩婺"䩘"㔗(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗套悩欆彄䕩京䔇床昖臵臯彍䂷悩婺"啺"㔗

臘膆嚟潡床昖臵臯麯䔇 NULL 镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗套悩婴婻臯凹庫䔇庖枕鄘䕩京婫麂䷺闼幽認婴臯䕩京套悩傂懟凹庫庖枕婉京婫麂䷺闼幽認婴臯婉京劥彍䂷悩滇橻䘖(NULL)㔗套悩懟婔臯䔇䂷悩鄘滇婉京潡 NULL 幽婫躿儏橬婔婻 NULL 闼幽 NOT IN 䔇䂷悩滇 NULL 㔗

9.16.4. ANY/SOME

expression operator ANY (subquery)
expression operator SOME (subquery)

埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂埻誫啂婔婻庖枕㔗噥膹臘膆嚟嘪䫘 operator 凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄噽䂷悩媙釂滇婄儫唚㔗套悩躿儏诙冖婔婻䩘唚彍 ANY 䂷悩婺"䩘"㔗套悩噘鄘诙冖啺唚彍䂷悩滇"啺"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗

SOME 滇 ANY 䔇劯懟臉㔗IN 京昽庯 = ANY 㔗

臙濘懟套悩澇橬诙冖傂嘘䩘唚幽婫躿儏橬婔婻埿膹臯婘臖淉嘩严婪䫘潊 NULL 闼幽 ANY 䔇䂷悩儖滇 NULL 蔯婉滇啺㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

启 EXISTS 婔湙啺螆床昖臵儖赆垯噘誊臯滇婉滯捺䔇㔗

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)

噥膹滇婔婻臯悇锹単(套誗4.2.11欔誄)埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂誫啂启噥膹臯悇锹単婔湙崔䔇庖枕㔗噥膹臘膆嚟嘪䫘 operator 凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄噽䂷悩媙釂滇婄儫唚㔗套悩躿儏诙冖婔婻䩘唚彍 ANY 䂷悩婺"䩘"㔗套悩噘鄘诙冖啺唚彍䂷悩滇"啺"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗

臙濘懟套悩澇橬诙冖傂嘘䩘唚幽婫躿儏橬婔婻埿膹臯婘臖淉嘩严婪䫘潊 NULL 闼幽 ANY 䔇䂷悩儖滇 NULL 蔯婉滇啺㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

昖䩋誗9.17.5诙埡噿庯锊臯懫膄䔇䂖誗㔗

9.16.5. ALL

expression operator ALL (subquery)

埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂埻誫啂婔婻庖枕㔗噥膹臘膆嚟嘪䫘 operator 凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄噽䂷悩媙釂滇婄儫唚㔗套悩噘鄘诙冖䩘唚ALL 䂷悩婺"䩘"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗套悩躿儏诙冖婔婻啺唚彍䂷悩滇"啺"㔗

臙濘懟套悩澇橬诙冖傂嘘啺唚幽婫躿儏橬婔婻埿膹臯婘臖淉嘩严婪䫘潊 NULL 闼幽 ALL 䔇䂷悩儖滇 NULL 蔯婉滇啺㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

NOT IN 京昽庯 <> ALL 㔗

启 EXISTS 婔湙啺螆床昖臵儖赆垯噘誊臯滇婉滯捺䔇㔗

row_constructor operator ALL (subquery)

噥膹滇婔婻臯悇锹単(套誗4.2.11欔誄)埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂誫啂启噥膹臯悇锹単婔湙崔䔇庖枕㔗噥膹臘膆嚟嘪䫘 operator 凹床昖臵䂷悩䔇懟婔臯誕臯婔渇螇䞖启懫膄噽䂷悩媙釂滇婄儫唚㔗套悩噘鄘诙冖䩘唚ALL 䂷悩婺"䩘"(寙拸床昖臵澇橬誫啂傂嘘臯䔇愙喕)㔗套悩躿儏诙冖婔婻啺唚彍䂷悩滇"啺"㔗

臙濘懟套悩澇橬诙冖傂嘘啺唚幽婫躿儏橬婔婻埿膹臯婘臖淉嘩严婪䫘潊 NULL 闼幽 ALL 䔇䂷悩儖滇 NULL 蔯婉滇啺㔗認婻臯婺镕䙓 SQL 崇䊖婄儫唚启 NULL 䂇劽施䔇蓇彍㔗

昖䩋誗9.17.5傖诙埡噿庯锊臯懫膄䔇䂖誗㔗

9.16.6. 锊臯懫膄

row_constructor operator (subquery)

噥膹滇婔婻臯悇锹単(套誗4.2.11欔誄)埿膹滇婔婻婖拸嚓拸蕙準䔇床昖臵垄媙釂誫啂启噥膹臯悇锹単婔湙崔䔇庖枕㔗蔯婫臖床昖臵婉脘誫啂轙誺 1 臯䂷悩(誫啂镽臯䕩嘷庯 NULL)㔗噥膹臘膆嚟凹床昖臵䔇嫇婔䂷悩臯誕臯螇䞖启懫膄㔗

昖䩋誗9.17.5傖诙埡噿庯锊臯懫膄䔇䂖誗㔗