SQL JOIN を使用すると、欠落したデータを簡単に見つけて修正できます - TechRepublic

以前の SQL レッスンに応えて、Michael M.やJason P.を含む TechRepublic メンバーが、SQL で使用できる JOIN の種類の基本的な説明を求めるコメントを投稿しました。

SQLコマンドラインを使ってテーブル内のレコードを検索すると、必要な情報を見つけるのにJOINが最も効果的なツールであることが分かるでしょう。今週は、JOINの基本的な種類とその仕組みについて説明します。

内部結合：一致するレコードのみ
2001年12月の「SQL入門講座」では、1つのステートメントで2つのテーブルから情報を抽出する方法をご紹介しました。SQLのほとんどの実装では、SELECT文を使って2つのテーブルから情報を取得します。SELECT
table1.column1, table1.column2,
table2.column1, table2.column2
FROM table1, table2
WHERE table1.keyfield=table2.keyfield

このような文を実行すると、SQLはtable1とtable2の内容に基づいて新しいテーブル（結果レコードセット）を作成します。結果に表示する2つのテーブルのフィールドを指定します。では、新しいテーブルにはいくつの行（レコード）が含まれるのでしょうか？

答えはWHERE句によって異なります。このサンプル文では、条件 (table1.keyfield=table2.keyfield) は、keyfieldで指定された列に一致する値を持つレコードを両方のテーブルから検索するようにSQLに指示しています。一方のテーブルに孤立したレコード（つまり、もう一方のテーブルに一致する値を持つレコードが存在しないレコード）が含まれている場合、それらのレコードは無視されます。

この動作を説明するために、図 Aに示す 2 つのテーブルを検討します。

図A

これら 2 つのテーブルを使用して、JOIN がクエリにどのように影響するかを説明します。

Donorsテーブルには5つのレコードがあり、それぞれが異なる人物を表しています。DonationRecordsテーブルには、DonorID列にJeff（DonorID 1）、Kim（DonorID 2）、Angela（Donor ID3）のエントリに加え、DonorIDが6の人物のエントリが3つあります。

ここで、次のSQLコマンドを実行すると何が起こるか考えてみましょう:
SELECT Donors.DonorID,Donors.Donorname,
DonationRecords.DonationAmt
FROM Donors, DonationRecords
WHERE Donors.DonorID=DonationRecords.DonorID

図Bはこのクエリの結果を示しています。WHERE句で、レコードセットにヒットするにはDonorIDフィールドのエントリが一致する必要があると指定されているため、DonorIDが6の寄付者の寄付レコードは無視されます。

図B

クエリの結果では、DonorID 6 に属する寄付レコードは無視されました。

理想的な設定では、孤立したレコードがDonationRecordsテーブルに入力されることはありません。しかし、この例は、クライアントが私に不正なデータや欠落データのクリーンアップを期待していた実際のケースに基づいています。このケースでは、寄付ファイル内の書類を監査し、DonorID 6に誰の名前を関連付けるべきかを判断しました。そして、その名前をDonorsテーブルに入力しました。

クエリのINNER JOINバージョン
両方のテーブルで一致する値を持つレコードのみが結果に含まれるこの種のクエリは、INNER JOINと呼ばれます。INNER JOIN構文を使用するには、クエリを次のように書き換えます。SELECT
Donors.DonorID,Donors.Donorname.
DonationRecords.DonationAmt
FROM Donors INNER JOIN DonationRecords
ON Donors.DonorID = DonationRecords.DonorID;

FROM Donors INNER JOIN DonationRecords ONという句は、SQLに、キーワードONに続く式に基づいて、2つのテーブル間で一致するレコードを検索するように指示します。この式は、最初の例のWHERE句で使用した式とよく似ています。

同じ種類のクエリを実行する方法が2つある場合、どちらを使用すべきでしょうか？答えは使用しているSQLのバージョンによって異なります。場合によっては、INNER JOIN構文の方が処理効率が良いこともあります。

LEFT JOIN: 一致するレコードと左側の孤立レコード
それでは、LEFT JOIN と INNER JOIN の違いは何でしょうか? LEFT JOIN 構文を使用してクエリを実行すると、SQL は次の 2 つの処理を行います。

• ON 句で定義されているように、一致する値を含む両方のテーブルのすべてのレコードを返します。
• また、右側のテーブルに一致する値がない場合でも、FROM…LEFT JOIN キーワードの左側のテーブルからすべてのレコードを返します。

この例では、LEFT JOIN は次のビジネス上の質問に答えます: 寄付者リストの中にまだ寄付をしていない人はいますか?

LEFT JOIN がどのように機能するかを説明するために、次のように、元の SELECT ステートメントの INNER JOIN を LEFT JOIN に変更してみましょう:
SELECT Donors.DonorID,Donors.Donorname.
DonationRecords.DonationAmt
FROM Donors LEFT JOIN DonationRecords
ON Donors.DonorID = DonationRecords.DonorID;

このステートメントを実行すると、図Cに示すような結果が得られます。Donorsテーブルが最初に記述されているため（つまりFROM…ON構文の左側にあるため）、LEFT JOINはDonationRecordsテーブルに対応するレコードがないにもかかわらず、寄付者IDがそれぞれ4と5であるMaryとShawnaのレコードを返しました。

図C

LEFT JOIN を実行すると、DonationRecords テーブルに対応するレコードがない場合でも、クエリによって Donors テーブル (左側のテーブル) のすべての値が返されました。

RIGHT JOIN: 一致するレコードと右側からの孤立レコード
RIGHT JOIN 構文を使用してクエリを実行すると、SQL は次の 2 つの処理を実行します。

• ON 句で定義されているように、一致する値を含む両方のテーブルのすべてのレコードを返します。
• また、左側のテーブルに一致する値がない場合でも、FROM…RIGHT JOIN キーワードの右側のテーブルからすべてのレコードを返します。

サンプルの SELECT ステートメントで LEFT JOIN を RIGHT JOIN に変更して、RIGHT JOIN がどのように機能するかを説明しましょう:
SELECT Donors.DonorID,Donors.Donorname.
DonationRecords.DonationAmt
FROM Donors RIGHT JOIN DonationRecords
ON Donors.DonorID = DonationRecords.DonorID;

このステートメントは、図Dに示すような結果を生成します。DonationRecordsテーブルが2番目（ FROM…ON構文の右側）に指定されているため、RIGHT JOINはDonorID 6のレコードを返しましたが、DonorsテーブルにはそのID番号を持つ人物に対応するレコードが存在しません。

図D

RIGHT JOINクエリは、DonationRecordsテーブルのすべての値を返します。丸で囲まれた値は、Donorテーブルに対応するエントリがないレコードの値です。

様々なSQLフレーバーでサポートされている他の種類のJOINもありますが、ほとんどのSQL実装ではINNER JOIN、LEFT JOIN、RIGHT JOIN構造がサポートされています。これら3つのクエリの仕組みを理解すれば、テーブル内の孤立したレコード（またはその他のダーティデータ）を見つけて修正するための非常に便利なツールになります。