これまでのクエリは、一度に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テーブルの一致しない行を無視します。 -
都市名を含む列が 2 つあります。 これは、
weathercitiesテーブルの列のリストが連結されているので正しいです。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のレコードを戻す方法を考えてみましょう。 クエリに実行させたいのは、weathercitiescities テーブルのカラムに「空の値」を代入します。 このようなクエリは外部結合と呼ばれます。 コマンドは次のようになります。
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;
このような省略のスタイルはよく見かけます。