【JPUG主催】PostgreSQLカンファレンス2020【11月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 つのテーブルにのみアクセスするものでした。問い合わせは、一度に複数のテーブルにアクセスすることも、同時にテーブル内の複数の行の処理を行う場合など、同じテーブルにアクセスすることも可能です。一度に同一のテーブルまたは複数のテーブルの複数の行にアクセスする問い合わせは、 結合 問い合わせと呼ばれます。例えば、関連する都市の位置情報を気象データと一緒に表示したい場合が挙げられます。このためには、weather テーブルの各行の city の列と、cities テーブルの全ての行の name 列を比較し、両者の値が一致する行の組み合わせを選択しなければなりません。

Note: これは概念的なモデルです。実際の結合はもっと効率的な方法で行われます。しかし、これはユーザからは判りません。

これは、以下の問い合わせによって行うことができます。

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 つのことが判ります。

練習: 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)

この問い合わせは 左外部結合 と呼ばれます。結合演算子の左側に指定したテーブルの各行が最低でも一度出力され、一方で、右側のテーブルでは、左側のテーブルの行に一致するもののみが出力されるからです。右側のテーブルに一致しない、左側のテーブルの行を出力する時、右側のテーブルの列は空の(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, 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 テーブルに W1W2 というラベルをつけています。また、入力を少なくするために、他の問い合わせでもこの種の別名を使用できます。以下に例を示します。

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

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