PostgreSQL 在表之間連接

到目前為止，我們的查詢一次只訪問一個表。查詢可以一次訪問多個表，或者用這種方式訪問一個表而同時處理該表的多個行。 一個同時訪問同一個或者不同表的多個行的查詢叫連接查詢。舉例來說，比如你想列出所有天氣記錄以及相關(guān)的城市位置。要實現(xiàn)這個目標，我們需要拿 weather表每行的city列和cities表所有行的name列進行比較， 并選取那些在該值上相匹配的行對。

這個任務(wù)可以用下面的查詢來實現(xiàn)：

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)

觀察結(jié)果集的兩個方面：

練習(xí)：.  看看這個查詢省略WHERE子句的語義是什么

因為這些列的名字都不一樣，所以規(guī)劃器自動地找出它們屬于哪個表。如果在兩個表里有重名的列，你需要限定列名來說明你究竟想要哪一個，如：

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

人們廣泛認為在一個連接查詢中限定所有列名是一種好的風(fēng)格，這樣即使未來向其中一個表里添加重名列也不會導(dǎo)致查詢失敗。

到目前為止，這種類型的連接查詢也可以用下面這樣的形式寫出來：

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

這個語法并不象上文的那個那么常用，我們在這里寫出來是為了讓你更容易了解后面的主題。

現(xiàn)在我們將看看如何能把Hayward記錄找回來。我們想讓查詢干的事是掃描weather表， 并且對每一行都找出匹配的cities表行。如果我們沒有找到匹配的行，那么我們需要一些“空值”代替cities表的列。 這種類型的查詢叫外連接 （我們在此之前看到的連接都是內(nèi)連接）。這樣的命令看起來象這樣：

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)

這個查詢是一個左外連接， 因為在連接操作符左部的表中的行在輸出中至少要出現(xiàn)一次， 而在右部的表的行只有在能找到匹配的左部表行時才被輸出。 如果輸出的左部表的行沒有對應(yīng)匹配的右部表的行，那么右部表行的列將填充空值（null）。

練習(xí)：.  還有右外連接和全外連接。試著找出來它們能干什么。

我們也可以把一個表和自己連接起來。這叫做自連接。 比如，假設(shè)我們想找出那些在其它天氣記錄的溫度范圍之外的天氣記錄。這樣我們就需要拿 weather表里每行的temp_lo和temp_hi列與weather表里其它行的temp_lo和temp_hi列進行比較。我們可以用下面的查詢實現(xiàn)這個目標：

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以區(qū)分連接的左部和右部。你還可以用這樣的別名在其它查詢里節(jié)約一些敲鍵，比如：

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

你以后會經(jīng)常碰到這樣的縮寫的。