今回はリレーショナルデータベースの特徴であるテーブル結合について紹介します
テーブル結合
前回作成したプラグインテーブルと作者テーブルを結合してみます
WordPressに新規テーブル作成 – HeidiSQLでデータベースらくらく開発
セルティスラボリレーショナルデータベースは、複数のテーブルを結合してデータを効率的に管理するシステムですが、データ処理方法や信頼性、パフォーマンス等何を重視するのかによりデータにどのような制約を設定するかは、ケースバイケースです (^^)
一般的にリレーションに外部キー制約 FOREIGN KEY を付けてテーブル間の結合に対してデータの整合性を保つ場合が多いですが、外部キー制約を指定していない場合には結合フィールドのデータ整合性は保たれていません
主なテーブル結合方法には INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION がありますが、今回は INNER JOIN と LEFT JOIN の結合方法を使い分けて様々なデータを抽出する方法を紹介します。
また、外部キー制約を設けていない場合の、代わりと言ってはなんですが、どこからも参照されなくなったレコードを LEFT JOIN 結合を使って削除する方法も紹介します
それでは、各結合方法について見ていきますが、イメージとしては集合で使うベン図を思い浮かべていただくと分かりやすいかなと思います
内部結合 INNER JOIN
テーブルAとテーブルBの結合するフィールド値が一致した各テーブルのレコードのみを結合して抽出します
図ではテーブルAとテーブルBの重なっている部分となります
結合フィールドデータの整合性が保たれていて、全て一致する場合で、テーブルAとテーブルBの結合フィールドは重なったイメージとなります
しかし、現実はデータの整合性が保たれていないこともあります。全てが不一致だと結合できない状態となります
例：プラグインテーブルと作者テーブルを内部結合します
結合フィールドには、作者ID を使用します
SELECT * FROM wp_plugins_ranking_base AS p INNER JOIN wp_plugins_ranking_author As a ON p.author_id = a.id
HeidiSQLで実行してみます
プラグインデータと作者データレコードが結合して一覧表示されました (^^)
この SQL は、簡単にファイルに保存/呼び出しすることが出来るので、よく使う SQL は保存しておくとデバッグ作業がはかどります
プラグイン検索では、この INNER JOIN 結合を使い、 WHERE で条件を付けて、カテゴリー分けや人気プラグイン、日本製プラグイン等の様々なデータを抽出しています
左外部結合 LEFT JOIN
テーブルAとテーブルBの結合フィールド値が一致するかどうかにかかわらず、テーブルAの全てのレコードを抽出します。
結合フィールドの値が一致すれば一致したテーブルBのレコードを結合し、一致しなければ NULL がセットされます
例：作者テーブルとプラグインテーブルを左外部結合
SELECT * FROM wp_plugins_ranking_author AS a LEFT JOIN wp_plugins_ranking_base As p ON a.id = p.author_id
結合フィールドのデータ整合性がとれている状態では、内部結合の場合と結果に違いはないのですが、今回はいくつかプラグインデータを削除して、作者テーブルに参照されないレコードを作ってみます
HeideSQLから簡単にレコードを削除出来るので、id 2のレコードを消してみます
削除したら、次に LEFT JOIN に WHERE で条件を付け author_id が NULL のものを抽出してみます
一致しなかった場合にセットされた NULL を活用します
SELECT * FROM wp_plugins_ranking_author AS a LEFT JOIN wp_plugins_ranking_base As p ON a.id = p.author_id WHERE p.author_id IS NULL
このSQLで削除したプラグインデータ２の作者データが抽出できます
外部結合で参照されなくなったレコードを削除
今回のプラグイン検索では、２年間以上更新がないプラグインのレコードを削除した時に、そのプラグインの作者データがテーブルに残ってどこからもアクセスされないデータとならないようにするために使用します
ポイントは、 DELETE の後にテーブル名を指定するのと、テーブル名の指定に AS による別名を使用しないところです
DELETE wp_plugins_ranking_author FROM wp_plugins_ranking_author LEFT JOIN wp_plugins_ranking_base ON wp_plugins_ranking_author.id = wp_plugins_ranking_base.author_id WHERE wp_plugins_ranking_base.author_id IS NULL
※SELECT 時のようにテーブルに AS で別名を指定するとエラーとなります (^_^;)
右外部結合 RIGHT JOIN
これは上記の反対で、テーブルBとテーブルAの結合フィールド値が一致するかどうかにかかわらず、テーブルBの全てのレコードを抽出します。
結合フィールドの値が一致すれば一致したテーブルAのレコードを結合し、一致しなければ NULL がセットされます。
処理的には、 LEFT JOIN と反対というだけなので以下 カッツアイ です
和結合 UNION
テーブルA,テーブルBの全てのレコードを抽出して結合します
一般的に FULL OUTER JOIN と呼ばれていますが、MySQL の場合は RIGHT JOINとLEFT JOINをUNIONすることで抽出することが出来ます
RIGHT JOINとLEFT JOINをUNIONする
SELECT * FROM wp_plugins_ranking_base LEFT JOIN wp_plugins_ranking_author ON wp_plugins_ranking_base.author_id = wp_plugins_ranking_author.id UNION SELECT * FROM wp_plugins_ranking_base RIGHT JOIN wp_plugins_ranking_author ON wp_plugins_ranking_base.author_id = wp_plugins_ranking_author.id
試しに実行してみましたが、かなり重い動作になったので、使用する場合は注意が必要そうです (>_<)
その他 Tips
ビュー
LEFT JOINのところで、結合するSQLをファイルとして簡単に保存/呼び出しする方法を紹介しましたが、ビューという仮想的なテーブルを作成する方法もあります
結合を行うSELECT文の結果をビューと言われる抽出したレコード、フィールドで構成される仮想的なテーブルの定義情報として保存すると、その仮想テーブルに対してSQLを実行することが出来るようになります
ビューで仮想テーブル作成
ビューで作成したテーブルにSQL発行
今回のプラグイン検索では使っていませんが、HeidiSQLでも簡単に使えるので、複雑なSELECTはビューとして保存したほうが扱いやすくなるかもしれません
以上
主なテーブル結合について紹介しました
結合って言ってもなかなかイメージしにくいですが、集合のベン図を思い浮かべると少しは分かりやすいかも知れません
私も基本的なSQLしかわかっていませんが、HeideSQLを使えば、簡単にいろんな動作を試せるのでお勧めです (^^)
次回は、インデックスの設定方法について紹介する予定です
