2.6. テーブル間を結合

ここまでの問い合わせは、一度に1つのテーブルにのみアクセスするものでした。 問い合わせは、一度に複数のテーブルにアクセスすることも、同時にテーブル内の複数の行の処理を行う場合など、同じテーブルにアクセスすることも可能です。 一度に同一のテーブルまたは複数のテーブルの複数の行にアクセスする問い合わせは、結合問い合わせと呼ばれます。 例として、関連する都市の位置情報を気象データと一緒に表示したい場合が挙げられます。 それを行うためには、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)

この結果から2つのことがわかります。

• Hayward市についての結果行はありません。 citiesテーブルにはHaywardに一致する項目がなく、結合の際にweatherテーブル内の一致しない行は無視されるからです。 これがどのようになされるのかを、簡単に見てみましょう。

• 都市名を持つ２つの列があります。 weatherテーブルとcitiesテーブルからの列のリストが連結されているためこのようになります。 しかし実際には、これは望ましい結果ではないため、*を使わずに、明示的に出力列のリストを指定することになります。

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

列はそれぞれ異なる名前ですので、パーサは自動的にどのテーブルの列かを見つけます。 2つのテーブルで列名が重複していた場合は、以下のようにどちらの列を表示させたいかを示すために列名を修飾しなければなりません。

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)

この問い合わせは左外部結合と呼ばれます。 結合演算子の左側に指定したテーブルの各行が最低でも一度出力され、一方で、右側のテーブルでは左側のテーブルの行に一致するもののみが出力されるからです。 右側のテーブルに一致しない、左側のテーブルの行を出力する時、右側のテーブルの列は空の値（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;

こういった形の省略はかなりよく行われます。