MySQLテーブル結合 – HeidiSQLでデータベースらくらく開発

今回はリレーショナルデータベースの特徴であるテーブル結合について紹介します

テーブル結合

前回作成したプラグインテーブルと作者テーブルを結合してみます

WordPressに新規テーブル作成 – HeidiSQLでデータベースらくらく開発
WordPress に新規テーブルを作成して、SQLによる基本的なアクセス方法についての紹介です。 wpdb クラスや dbDelta 関数についても紹介しています (^^)

リレーショナルデータベースは、複数のテーブルを結合してデータを効率的に管理するシステムですが、データ処理方法や信頼性、パフォーマンス等何を重視するのかによりデータにどのような制約を設定するかは、ケースバイケースです (^^)

一般的にリレーションに外部キー制約 FOREIGN KEY を付けてテーブル間の結合に対してデータの整合性を保つ場合が多いですが、外部キー制約を指定していない場合には結合フィールドのデータ整合性は保たれていません

主なテーブル結合方法には INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION がありますが、今回は INNER JOIN と LEFT JOIN の結合方法を使い分けて様々なデータを抽出する方法を紹介します。

また、外部キー制約を設けていない場合の、代わりと言ってはなんですが、どこからも参照されなくなったレコードを LEFT JOIN 結合を使って削除する方法も紹介します

それでは、各結合方法について見ていきますが、イメージとしては集合で使うベン図を思い浮かべていただくと分かりやすいかなと思います

 

内部結合 INNER JOIN

テーブルAとテーブルBの結合するフィールド値が一致した各テーブルのレコードのみを結合して抽出します

図ではテーブルAとテーブルBの重なっている部分となります

inner_join_1

結合フィールドデータの整合性が保たれていて、全て一致する場合で、テーブルAとテーブルBの結合フィールドは重なったイメージとなります

inner_join_2

しかし、現実はデータの整合性が保たれていないこともあります。全てが不一致だと結合できない状態となります

inner_join_3

 

例:プラグインテーブルと作者テーブルを内部結合します

結合フィールドには、作者ID を使用します

SELECT * FROM wp_plugins_ranking_base AS p INNER JOIN wp_plugins_ranking_author As a ON p.author_id = a.id

 

HeidiSQLで実行してみます

プラグインデータと作者データレコードが結合して一覧表示されました (^^)

heidisql_inner_join

この SQL は、簡単にファイルに保存/呼び出しすることが出来るので、よく使う SQL は保存しておくとデバッグ作業がはかどります

sql_savefile

プラグイン検索では、この INNER JOIN 結合を使い、 WHERE で条件を付けて、カテゴリー分けや人気プラグイン、日本製プラグイン等の様々なデータを抽出しています

 

 左外部結合 LEFT JOIN

テーブルAとテーブルBの結合フィールド値が一致するかどうかにかかわらず、テーブルAの全てのレコードを抽出します。

結合フィールドの値が一致すれば一致したテーブルBのレコードを結合し、一致しなければ NULL がセットされます

left_join

 

例:作者テーブルとプラグインテーブルを左外部結合

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のレコードを消してみます

heidisql_del_record

削除したら、次に 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で削除したプラグインデータ2の作者データが抽出できます

heidisql_left_join_null

 

外部結合で参照されなくなったレコードを削除

今回のプラグイン検索では、2年間以上更新がないプラグインのレコードを削除した時に、そのプラグインの作者データがテーブルに残ってどこからもアクセスされないデータとならないようにするために使用します

ポイントは、 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 がセットされます。

right_join

処理的には、 LEFT JOIN と反対というだけなので以下 カッツアイ です

 

和結合 UNION

テーブルA,テーブルBの全てのレコードを抽出して結合します

full_outer_join

一般的に 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を実行することが出来るようになります

ビューで仮想テーブル作成

heidisql_view

ビューで作成したテーブルにSQL発行

heidisql_view_select

今回のプラグイン検索では使っていませんが、HeidiSQLでも簡単に使えるので、複雑なSELECTはビューとして保存したほうが扱いやすくなるかもしれません

 

以上

主なテーブル結合について紹介しました

結合って言ってもなかなかイメージしにくいですが、集合のベン図を思い浮かべると少しは分かりやすいかも知れません

私も基本的なSQLしかわかっていませんが、HeideSQLを使えば、簡単にいろんな動作を試せるのでお勧めです (^^)

次回は、インデックスの設定方法について紹介する予定です

 


まとめ記事紹介

search star user home refresh tag chevron-left chevron-right exclamation-triangle calendar comment folder thumb-tack navicon angle-double-up angle-double-down angle-up angle-down quote-left googleplus facebook instagram twitter rss