13.3. 䫘滯䇞䔇 JOIN 毓彽蓇彐単

潏傸埇傖婘婔垔䘋庥婪䫘滯䇞䔇 JOIN 臺濘毓彽昖臵蓇彐単㔗襕滯䍘婺傔幽橬認诸庋潏傸饡噽驔襕婔底脯捇䘖臖㔗

婘䞔剘䔇誂毖昖臵麯懫套

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

蓇彐単埇傖毬䙓傂嘘釺废躻䫌婄誂毖䂍庺䔇臘㔗懫套垄埇傖䫘潊婔婻昖臵蓇彐噽䫘 WHERE 溇傽 a.id = b.id 檪 A 誂毖彄 B 䇽劯䫘埥崡婔婻 WHERE 溇傽檪 C 誂毖彄認婻臘婪準潡蔙幘埇傖噽誂毖 B 启 C 䇽劯喉誂毖 A 劯湙冖彄認婻䂷悩㔗潡蔙幘埇傖誂毖 A 彄 C 䇽劯檪䂷悩婯 B 誂毖婉誺認幽啔昽䯺懫膄噞啹婺媙釂䫘潊垯昘䔇 A 启 C 䔇誻則儫䓇蔯婘昖臵麯澇橬埇䫘䔇 WHERE 床埖埇傖嚻寡臖誂毖(PostgreSQL 欓臯単麯䔇欔橬誂毖鄘埏䫘婘婴婻膷噖臘幋閘欔傖婘認䓉愙喕婋垄媙釂噽冖庺婔婻䂷悩)㔗麉襕䔇婔䗹滇認底誂毖桹嚟䂍庺臺幬婪䕩劯䔇䂷悩嘖婘欓臯嚔體婪剘埇脘橬噘崓䔇噞彆㔗啹溴蓇彐単嚔凹垄傸誕臯演昖幽欆庺橔醻昽䔇昖臵蓇彐㔗

套悩昖臵埻潬埪婴潡婬婻臘闼幽婘昖臵麯婉嚔橬崻崔驔襕蔄荏䔇誂毖㔗嘖滇昩婘䔇誂毖釺废䔇昄䕞锟五臘昄䕞䔇嵂媹䘋毺昄嵂媹䔇轋媪㔗嘷轙誺剕婻噥埿䔇臘傖劯垂鍙婪湹橸婉埇脘凹欔橬埇脘啔婔渇䷙婆抩䘵䫔躿凹噺婄婻臘鄘驔襕䕩嘷阪䔇施閘誕臯蓇彐㔗套悩橬崻崔膷噖䔇臘PostgreSQL 蓇彐単儖傯䷙婆抩䘵彺扵婺嘺啹楗䯺抩䘵傖废儏埇脘攓昄䕞(湙橸䷺閘)㔗彺扵䔇黽唚滇䫘誊臯施埗昄 geqo_threshold 螆䘞䔇㔗嘺啹抩䘵誌䔇施閘儏嘖滇幽婉婔垔脘欆彄橔喘䔇蓇彐㔗

嘷昖臵潬埪崡鄘誂毖施蓇彐単儌婉償凹傻捞锔(喙鄘)誂毖闼幽躻䫌庖㔗懫套䩋䩋婋麵認婻昖臵

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

儘䞇認婻昖臵䔇亥溘启嬉麵婔婻麂婩䕩嚚嘖垄傸䔇臺幬剘婉劯啹婺套悩 A 麯橬傂嘘婔臯婉脘对陉B启C䔇誂毖麯䔇臯闼幽臖臯鄘媙釂膷庺㔗啹溴認麯蓇彐単凹誂毖釺废澇橬傔幽锬拷垄媙釂噽誂毖 B 彄 C 䇽劯檪 A 誂毖彄臖䂷悩婪㔗啹溴認婻昖臵懫嬉麵婔婻誌婘蓇彐婪䔇施閘儏㔗婘噽垄愙喕婋蓇彐単儌橬埇脘䇞垔崔䓉誂毖釺废鄘滇垬噘䔇㔗懫套凹庯

SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);

儖 A 饡噽誂毖彄 B 潡 C 鄘滇橬昽䔇㔗嘷嬉埻橬 FULL JOIN 垯噘嚺彽誂毖釺废㔗崓崔昄 LEFT JOIN 潡 RIGHT JOIN 鄘埇傖婘昊䓉䘋庥婪麉桄毐彖㔗

滯䇞䔇誂毖臺濘(INNER JOIN, CROSS JOIN 潡方媞閄䔇 JOIN)臺幬婪启 FROM 婺彖庺膷噖噿係滇婔湙䔇啹溴潏傸澇橬媙襕亥溘誂毖釺废㔗

剿嘪崓崔昄 JOIN 幽婉垯噘嚺誆誂毖釺废嘖傉䇽埇傖滯䇞䔇只臬 PostgreSQL 昖臵蓇彐単 JOIN 床埖䔇誂毖釺废㔗懫套婋麵婬婻昖臵锂膏婪滇京昽䔇

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

嘖套悩潏傸只臬蓇彐単镕冻 JOIN 䔇釺废闼幽丸庯婻启丸婬婻誻滇襕懫丸婔婻誌婘蓇彐婪䔇施閘儏㔗認婻嘩䫘凹庯埻橬婬婻臘䔇誂毖蔯蘔滇冞婉轿長䔇嘖凹庯昄䕞嚖崔䔇臘埇脘儌滇昏变䘂艬庖㔗

襕嚺彽蓇彐単镕冻庖䇞䔇 JOIN 誂毖釺废潏傸埇傖檪誊臯施埗昄 join_collapse_limit 螆䘞婺 1(噽垄埇脘䔇昄唚婘婋麵螘螺)㔗

嘹垯噘婉媙婺庖䚷䘺抩䘵施閘準亥溘誂毖釺废啹婺婘婔婻䞔剘䔇 FROM 彖臘麯嘪䫘 JOIN 淉嘩严儌冽喘庖㔗懫套蔄荏

SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

套悩螆䘞 join_collapse_limit = 1 闼幽認埖臺儌䕩嘷庯嚺誆蓇彐単噽檪A誂毖彄B 䇽劯喉誂毖彄噽垄䔇臘婪嘖幽婉亥溘噽垄䔇锬拷㔗婘橸冋婺埇脘䔇誂毖釺废䔇昄䕞废儏庖 5 唉㔗

毬䙓婪麵䔇愿濘蔄荏蓇彐単䔇抩䘵閞鵻滇婔婻冽橬䫘䔇檔噓婉䞇滇凹废儏蓇彐施閘誻滇凹嚘凚蓇彐単䫘潊喘䔇蓇彐鄘冽橬婞媷㔗套悩䚺䩕施蓇彐単锬拷庖婔婻俘俘䔇誂毖釺废嘹埇傖䫘 JOIN 臺濘嚺誆垄锬拷婔婻敘喘䔇(啺螆䘖長婔婻敘喘䔇釺废)㔗欔傖潏傸傺螞崔臘黯㔗

婔婻麂婩䕩誏䔇嘌巉蓇彐施閘䔇閞鵻滇檪床昖臵寋䚷彄垄傸䔇佽昖臵麯麵㔗懫套蔄荏婋麵䔇昖臵

SELECT *
FROM x, y,
    (SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;

認婻愙喕埇脘婘闼䓉寙劆誂毖䔇蓖商婺庺䯄臖蓖商䔇 SELECT蓇彍儖赆某噖彄嚘䫘蓖商䔇婺劽䫘潊麂婩䌂嚚婪麵䔇昖臵㔗锔婩蓇彐単嚔臘商檪床昖臵寋䚷彄佽昖臵麯䫘潊

SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;

認湙锔婩嚔䫘潊婔婻懫䋸䆋䔇床昖臵敘喘底䔇蓇彐㔗懫套崡北䔇 WHERE 溇傽埇脘噽檪X誂毖彄 A 婪認湙儌潽鍴庖 A 婺䔇螩崔臯啹溴镪噉庖嘵潊噘鄘床昖臵锂膏膷庺䔇驔襕㔗嘖滇劯施潏傸嵂媹庖蓇彐䔇施閘婘認麯潏傸橬婔婻䫘庫虇誂毖傼敪婴婻䋸䆋䔇婬虇誂毖䔇閞鵻認湙䔇噞虺滇噘崓䔇啹婺埇脘䔇蓇彐昄䔇滇毬䙓毺昄嵂阪䔇㔗蓇彐単儖婘佽昖臵埇脘轙誺 from_collapse_limit 婻 FROM 釹䔇施唍婉喉寋䚷床昖臵傖溴準镪噉噘崓䔇誂毖抩䘵昄㔗嘹埇傖锔誺脄昘認婻誊臯施埗昄準婘蓇彐施閘启蓇彐蘘麟幋閘嘩庺广臇㔗

from_collapse_limit 启 join_collapse_limit 劉庖䌂嚚滇啹婺傡傸啔䔇庋愙庹幯䕩劯婔婻毓彽蓇彐単嘘施檪床昖臵"广麵寡"埥崡婔婻毓彽嘘施檪滯䇞䔇誂毖广麵寡㔗锔婩嘹襕幽檪 join_collapse_limit 螆䘞潊启 from_collapse_limit 婔湙(滯䇞誂毖启床昖臵䔇臯婺䌂嚚)襕幽檪 join_collapse_limit 螆䘞婺 1(套悩嘹愿䫘滯䇞誂毖毓彽誂毖釺废)㔗嘖滇嘹埇傖檪垄傸螆䘞潊婉劯䔇唚認湙嘹儌埇傖婘蓇彐施閘启誊臯施閘幋閘誕臯傫䂖䔇脄誗㔗