テーブルの結合（JOIN）

MySQL ver 5.6.38を使用しています

テーブルとテーブルの結合のお話。

テーブルAの情報とテーブルBの情報をいっぺんにSELECTしたいとき、ありますよね
そんな時にJOINします

種類としては2コ覚えればいいと思います

ずばり、内部結合と外部結合です

 

内部結合（JOIN）

正確にはINNER JOIN、でもJOINと書いても一緒です

説明するより、実際にやってみるのが早いので、

例として、employeeとwork_locationという2コのテーブルをJOINします。

2つのテーブルに以下のようなデータが入っている前提

以下のSQLで結合してSELECT

SELECT
  * 
FROM
  `employee` JOIN `work_location` 
    ON `employee`.`id` = `work_location`.`employee_id`;

結果、以下のようにSELECTされます

employeeテーブルのidが4と5の田中さん、伊藤さんが、work_locationテーブルに存在しないので、結果に出てこないのが内部結合の特徴です。

田中さんと伊藤さんは内部結合から仲間外れにされてしまいます・・・

 

【解説】

1～3行目はただのSELECT * FROMなので、飛ばして

■4行目

`employee` JOIN `work_location`

employeeテーブルにwork_locationを結合したいので、JOIN句を挟んで並べます

 

■5行目

ON `employee`.`id` = `work_location`.`employee_id`;

それぞれのテーブルのレコードを紐づけさせるカラムをONの後に指定します

今回はemployeeテーブルのidカラムとwork_locationテーブルのemployee_idカラムの2つで、合致するレコードが紐づいています

書き方は、ONの後に

ON `table_nameA`.`col_nameA` = `table_nameB`.`col_nameB`

です。

複数のカラムを結合条件にしたい場合は、続いて、ANDの後に同じように指定します。

ON `table_nameA`.`col_nameA` = `table_nameB`.`col_nameB`
AND `table_nameA`.`col_nameC` = `table_nameB`.`col_nameD`

みたいな感じ

 

外部結合（LEFT JOIN、RIGHT JOIN）

続いて外部結合、コイツも本来はOUTER JOINですが、OUTERは省略しても一緒です

同じようにemployeeとwork_locationテーブルで試してみましょう

構文はJOINの前にLEFTを追加するだけ

 

SELECT
  * 
FROM
  `employee` LEFT JOIN `work_location` 
    ON `employee`.`id` = `work_location`.`employee_id`

 

今度は以下のようにSELECTされます

 

さっきの内部結合と違って、田中さん伊藤さんも結果に出てきますね

ただ、work_locationに合致するレコードがないので、値はNULLになっています。

work_locationに合致しなくても、employeeにレコードがあれば、NULLで持ってくるのが外部結合の特徴です。

 

また、RIGHT JOINも性質は一緒です

違いはどっちのテーブルをベースにして結合するか否か

試しにLEFTをRIGHTに変えてみます

 

LEFT JOINの時のNULLレコードがなくなっていますね

これはwork_locationをベースにして、employeeをくっつけているから。

work_locationにそもそも存在しないレコードはSELECTされません

 

最後にJOIN時のサブクエリも少しご紹介

サブクエリ

例えば、テーブルAに対してWHERE句を指定してSELECTした結果や、また別のテーブルと結合した結果などを基にして、さらにJOINしたい場合もあります

そんなときはサブクエリ

例として、employeeのidを1と2のみにして、そのあとにJOINするパターンをご紹介

 

SELECT
  * 
FROM
  (SELECT
     * 
   FROM `employee` 
   WHERE `id` < 3) as t1
     JOIN `work_location` 
       ON t1.`id` = `work_location`.`employee_id`

 

4～7行目の（）で囲まれた中に、もう1コSQL文がありますよね

ここがサブクエリ、employeeテーブルのidについて、1と2だけSELECTしています

この結果を7行目のasを挟んで、t1という別名をつけました

つまりは、t1（idが1,2だけのemployee）とwork_locationの結合です

 

結果は以下

ただ、結合やサブクエリはやりすぎると性能が悪くなって激遅になるので要注意！

以前勤めていた現場では、ローカルPCシステム時代に使っていたレガシーSQLを捨てられずに、SaaS化、機能拡張、ユーザ増などを繰り返していたために、5コも10コもJOINしたSQLをVIEWにして、さらにそいつにJOINしてJOINしてJOINして・・・

てなSQLが蔓延していたので、繁忙期になるとDBサーバ君が悲鳴をあげ、必ずシステムが遅延、阿鼻叫喚。

からの悪玉SQL探して～、チューニングして～、お客さんに怒られて～、で徹夜はアタリマエのまさに地獄でした。

性能の話は始めると深すぎますが、全部作り終わった後に地獄を見る前に、ちょっとずつ気にしていった方が吉ですね

まあ、どえらい性能問題に直面しないとなかなか気をつけられないんですが