PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔	䆹2. SQL 臺蘔	媆誕	嬉誕

2.6. 婘臘幋閘誂毖

彄䕞嬉婺溵潏傸䔇昖臵婔渇埻螪閞庖婔婻臘㔗昖臵埇傖婔渇螪閞崔婻臘潡蔙䫘昊䓉桹嚟螪閞婔婻臘蔯劯施崇䊖臖臘䔇崔婻臯㔗婔婻劯施螪閞劯婔婻潡蔙婉劯臘䔇崔婻臯䔇昖臵埆誂毖昖臵㔗婆冋準臘懫套嘹愿彖庺欔橬崷愫螄嘘傖埪認底螄嘘䕩噿䔇嘯婗㔗襕垂䯄認婻䕞湺潏傸驔襕拪 weather 臘懟臯䔇 city 庖枕启 cities 臘欔橬臯䔇 name 庖枕誕臯懫膄幽锬埡闼底認底昄唚䕩对陉䔇臯㔗

㔊濘懟㔏認麯埻滇婔婻楗媕婪䔇昇傋㔗誂毖锔婩傖懫垂鍙懫膄懟婻埇脘䔇陉凹臯敘醻昽䔇桹嚟欓臯嘖認底滇䫘潙䩋婉彄䔇㔗

認婻傂媇埇傖䫘婋麵䔇昖臵準垂䯄

SELECT *
    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

蓗凘䂷悩镖䔇婴婻桹麵

澇橬嘯婗 Hayward 䔇䂷悩臯㔗認滇啹婺婘 cities 臘麯麵澇橬婯 Hayward 对陉䔇臯欔傖誂毖媘䘖庖 weather 臘麯䔇婉对陉臯㔗潏傸䘉劯儖䩋彄套嘘媞臖認婻閞鵻㔗
橬婴婻庖枕寙劆嘯婗劉㔗認滇溼䇞䔇啹婺 weather 启 cities 臘䔇庖枕滇毖婘婔蕙䔇㔗婉誺垂鍙婪潏傸婉愿襕認底啹溴嘹儖埇脘婯橕滯䇞彖庺膷庺庖枕蔯婉滇嘪䫘 * 
```
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;
```

㔊䂄幹㔏䩋䩋䩕䘖 WHERE 床埖䔇劆幬滇傔幽㔗

啹婺認底庖枕䔇劉庖鄘婉婔湙欔傖彖悊単躻媘欆庺垄傸匂庯巻婻臘嘖滇套悩婴婻臘婺橬麉崉䔇庖枕劉嘹儌媙釂嘪䫘庖枕噘䓄鍊垔嘹愿襕䔇庖枕

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

婔轸螴婺婘誂毖昖臵麯嘪䫘庖枕噘䓄滇冽喘䔇鼯湚認湙剿嘪婘儖準劏噽婺婔婻臘麯時媹庖劯劉庖枕幘婉嚔嚘蕙晙晖㔗

彄䕞嬉婺溵認䓉䌂傋䔇誂毖昖臵幘埇傖䫘婋麵認湙䔇嘵嚟喍庺準

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

認婻臺濘幽麂償婪麵闼婻闼幽婩䫘潏傸婘認麯喍庺準滇婺庖螷嘹敘垹滷庖蓼劯麵䔇婂鵻㔗

䯄婘潏傸儖䩋䩋套嘘脘檪 Hayward 螄嘘欆啂準㔗潏傸愿螷昖臵幾䔇庋滇欆柟 weather 臘幽婫凹懟婔臯鄘欆庺对陉䔇 cities 臘麯麵䔇臯㔗套悩澇橬欆彄对陉䔇臯闼幽驔襕婔底"䷺唚"傼敪 cities 臘䔇庖枕㔗認䓉䌂傋䔇昖臵埆 崡誂毖(潏傸婘溴幋嬉䩋彄䔇誂毖鄘滇喙誂毖)㔗認湙䔇变傴䩋蕙準償認湙

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

認婻昖臵滇婔婻噥崡誂毖啹婺誂毖淉嘩严(LEFT OUTER JOIN)噥膹䔇臘婺䔇臯婘膷庺婺躿儏庺䯄婔渇蔯埿膹䔇臘婺䔇臯埻膷庺闼底婯噥膹䔇臘橬对陉䔇臯㔗套悩膷庺䔇噥臘婺䔇臯澇橬埿臘婺䔇臯婯噽凹庫闼幽埿臘婺䔇庖枕儖准噙婺 NULL 㔗

㔊䂄幹㔏誻橬埿誂毖启噘誂毖㔗臘五欆庺準垄傸脘幾傔幽㔗

潏傸幘埇傖檪婔婻臘启躻噌誂毖蕙準㔗認埆躻誂毖㔗懫套啺螆潏傸愿欆庺闼底婘噽垄崷愫螄嘘䔇橷庥评啘幋崡䔇崷愫螄嘘㔗認湙潏傸儌驔襕拪 weather 臘麯懟臯䔇 temp_lo 启 temp_hi 庖枕婯 weather 臘麯噽垄臯䔇 temp_lo 启 temp_hi 庖枕誕臯懫膄㔗潏傸埇傖䫘婋麵䔇昖臵垂䯄認婻䕞湺

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

婘認麯潏傸檪 weather 臘麉桄湺螄婺 W1 启 W2 傖寺彖誂毖䔇噥膹启埿膹㔗嘹誻埇傖䫘認湙䔇彆劉婘噽垄昖臵麯誗亥婔底显髞懫套

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

傖劯嚔䂟婩䵄彄認湙䔇䚷喍㔗

劯锔	饡釕	嬉誕
昖臵婔婻臘	婪婔亓	蕔镖庘昄