他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

2.6. テーブル間を結合

ここまでの問い合わせは、一度に1つのテーブルにのみアクセスするものでした。 問い合わせは、一度に複数のテーブルにアクセスすることも、テーブル内の複数行の処理を同時に行うようなやり方で、1つのテーブルにアクセスすることも可能です。 一度に複数のテーブル(または同一テーブルの複数インスタンス)にアクセスする問い合わせは、結合問い合わせと呼ばれます。 それらは1つのテーブルからの行を2つ目のテーブルからの行と、どの行同士を組み合わせるかを指定する式により、結び付けます。 例えば、すべての気象データを関連する都市の位置情報と一緒にすべて返すためには、データベースはweatherテーブルの各行のcity列を、citiesテーブルの全ての行のname列と比較することが必要です。 [4] これは、以下の問い合わせによって行うことができます。

SELECT * FROM weather JOIN cities ON 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つのことに注目してください。

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

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

結合問い合わせではすべての列名を修飾するのが良いやり方であると一般に考えられています。 そうすれば、テーブルのいずれかに後で重複する名前を持つ列が追加されても、問い合わせが失敗しません。

ここまでに示したような結合問い合わせは、以下のような形で表すことができます。

SELECT *
    FROM weather, cities
    WHERE city = name;

この構文はJOIN/ONより以前のもので、SQL-92で導入されました。 テーブルはFROM句に単に列挙され、比較式はWHEREに追加されます。 この古い暗黙の構文と新しい明示的なJOIN/ON構文の結果は同一です。 ですが、問い合わせを読む方にしてみれば、明示的な構文の方がその意味をより理解しやすいです。 結合条件はそれ独自のキーワードにより導入されるのに対して、以前は条件は他の条件と一緒にWHERE句の中に混ざっていました。

ここで、どのようにすれば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_lotemp_hiを、他のweather行のtemp_lotemp_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 JOIN weather w2
        ON 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テーブルにw1w2というラベルを付けています。 また、入力量を省くために、他の問い合わせでもこの種の別名を使用できます。 以下に例を示します。

SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

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



[4] これは概念的なモデルでしかありません。 実際の結合は通常、1つひとつの行の組み合わせを比べるよりも、もっと効率的な方法で行われます。 しかし、これはユーザからはわかりません。