INNER JOINとOUTER JOIN
最近、SQL文を直接書く機会があって、今までほとんど全くSQLについて勉強する機会がなかったので業務に関係のあるところを少しかじってみることに。
今までいかにActiveRecord先生にお世話になっていたかがよくわかりました。
さてさて、それでは本題。
INNER JOINとOUTER JOINの違いについて理解を深めていきます。
以下のテーブルに対してSQLを発行してその結果を見ていくことにします。
mysql> select * from dogs; +----+-------+----------+ | id | name | owner_id | +----+-------+----------+ | 1 | aka | 1 | | 2 | ao | 2 | | 3 | shiro | 1 | | 4 | kuro | 4 | +----+-------+----------+ 4 rows in set (0.02 sec) mysql> select * from owners; +----+---------------+ | id | name | +----+---------------+ | 1 | ichiro | | 2 | jiro | | 3 | saburo | +----+---------------+ 3 rows in set (0.01 sec)
内部結合（INNER JOIN）
内部結合では左右それぞれのテーブルの指定したカラムの値が一致するレコードだけを取得します
基本となる構文は次の通りです。
SELECT table_name.col_name, ... FROM tbl_name1 INNER JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
結合の対象となるテーブルをFROMの後とINNER JOINの後に指定します。
そしてそれぞのテーブルの結合するカラムをONの後にイコール(=)で結んで指定します。
内部結合ではそれぞれのテーブルの指定したカラムの値が同じものを1つのデータとして取得します。どちらかのテーブルにしか値が存在しないデータは取得されません。
1. 左（dogs）のテーブルの一覧を取得し、
2. 右（owners）テーブルから、ONで指定した条件にマッチするレコードを探してマージする。
3. マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにする。
4. 左（dogs）のテーブル一覧のうち、条件にマッチするレコードがないものは削除される。
例：
mysql> select * from dogs inner join owners on dogs.owner_id = owners.id; +----+-------+----------+----+---------------+ | id | name | owner_id | id | name | +----+-------+----------+----+---------------+ | 1 | aka | 1 | 1 | ichiro | | 3 | shiro | 1 | 1 | ichiro | | 2 | ao | 2 | 2 | jiro | +----+-------+----------+----+---------------+ 3 rows in set (0.01 sec)
外部結合（OUTER JOIN）
外部結合は左右それぞれのテーブルの指定したカラムの値が一致するレコードに加えてどちらかのテーブルにしか存在しないデータについても取得します
基本となる構文は次の通りです。
SELECT table_name.col_name, ... FROM tbl_name1 LEFT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
SELECT table_name.col_name, ... FROM tbl_name1 RIGHT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
基本的に内部結合の場合と同じですが、外部結合では結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がどちらかのテーブルにしかなかった場合でもデータとして取得します。
この時、どちらのテーブルのデータを取得するかで2つの構文が用意されています。
LEFT JOINではFROMの後に書かれたテーブルのデータだけを取得します。RIGHT JOINではJOINの後に書かれたテーブルのデータだけを取得します。
LEFT OUTER JOIN
左側のテーブルを軸にして外部結合する。左側に指定されたテーブルのすべてのレコードが取得されます
1. 左（dogs）のテーブルの一覧を取得し、
2. 右（owners）テーブルから、ONで指定した条件にマッチするレコードを探してマージします。
3. マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにします。
4. 左（dogs）のテーブル一覧のうち、条件にマッチするレコードがないものの値はNULLになり、レコードは削除されません。
例：
mysql> select * from dogs left join owners on dogs.owner_id = owners.id; +----+-------+----------+-------+---------------+ | id | name | owner_id | id | name | +----+-------+----------+-------+---------------+ | 1 | aka | 1 | 1 | ichiro | | 2 | ao | 2 | 2 | jiro | | 3 | shiro | 1 | 1 | ichiro | | 4 | kuro | 4 | NULL | NULL | +----+-------+----------+-------+---------------+ 4 rows in set (0.01 sec)
RIGHT OUTER JOIN
右側のテーブルを軸にして外部結合する。右側に指定されたテーブルのすべてのレコードが取得されます
1. 右（owners）のテーブルの一覧を取得し、
2. 左（dogs）テーブルから、ONで指定した条件にマッチするレコードを探してマージします。
3. マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにします。
4. 右（owners）のテーブル一覧のうち、条件にマッチするレコードがないものの値はNULLになり、レコードは削除されません。
例：
mysql> select * from dogs right join owners on dogs.owner_id = owners.id; +------+-------+----------+------+---------------+ | id | name | owner_id | id | name | +------+-------+----------+------+---------------+ | 1 | aka | 1 | 1 | ichiro | | 3 | shiro | 2 | 1 | ichiro | | 2 | ao | 1 | 2 | jiro | | NULL | NULL | NULL | 3 | saburo | +------+-------+----------+------+---------------+ 4 rows in set (0.01 sec)
