準備
以下の2つのテーブルを準備する。
- students
- 学生IDと学生名を保存。
- courses
- 学科IDと学科名を保存。
studentsテーブルの作成とデータ登録のクエリー。
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE students ( id INT, name VARCHAR(10) ); INSERT INTO students VALUES (1, '安藤'), (2, '伊藤'), (3, '宇藤'), (4, '江藤'); |
実行結果。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT * FROM students; +------+--------+ | id | name | +------+--------+ | 1 | 安藤 | | 2 | 伊藤 | | 3 | 宇藤 | | 4 | 江藤 | +------+--------+ 4 rows in set (0.00 sec) |
coursesテーブルの作成とデータ登録のクエリー。
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE courses ( id INT, name VARCHAR(10) ); INSERT INTO courses VALUES (10, '数学'), (11, '物理学'), (12, '化学'), (13, '天文学'); |
実行結果。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT * FROM courses; +------+-----------+ | id | name | +------+-----------+ | 10 | 数学 | | 11 | 物理学 | | 12 | 化学 | | 13 | 天文学 | +------+-----------+ 4 rows in set (0.00 sec) |
JOIN～総当たり
FROM句のテーブルに対して単にJOINでテーブルを指定すると、2つのテーブルの全要素の組み合わせが得られる。
|
1 2 3 |
SELECT students.name, courses.name FROM students JOIN courses; |
実行結果。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
+--------+-----------+ | name | name | +--------+-----------+ | 江藤 | 数学 | | 宇藤 | 数学 | | 伊藤 | 数学 | | 安藤 | 数学 | | 江藤 | 物理学 | | 宇藤 | 物理学 | | 伊藤 | 物理学 | | 安藤 | 物理学 | | 江藤 | 化学 | | 宇藤 | 化学 | | 伊藤 | 化学 | | 安藤 | 化学 | | 江藤 | 天文学 | | 宇藤 | 天文学 | | 伊藤 | 天文学 | | 安藤 | 天文学 | +--------+-----------+ 16 rows in set (0.00 sec) |
結合テーブル
studentsテーブルとcoursesテーブルの結合テーブルを準備する。各学生の履修学科に関するデータ。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE student_courses ( student_id INT, course_id INT ); INSERT INTO student_courses VALUES (1, 10), (1, 11), (3, 10), (3, 11), (3, 13), (4, 11), (4, 13); |
クエリーの実行結果、
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT * FROM student_courses; +------------+-----------+ | student_id | course_id | +------------+-----------+ | 1 | 10 | | 1 | 11 | | 3 | 10 | | 3 | 11 | | 3 | 13 | | 4 | 11 | | 4 | 13 | +------------+-----------+ 7 rows in set (0.00 sec) |
INNER JOIN
結合テーブルに基づいて、2つのテーブルの両方に存在するデータを取り出す。studentsとcoursesの積集合に相当する。INNERは書かなくても内部結合になる。
|
1 2 3 4 5 6 7 |
SELECT カラム1, カラム2, ..... FROM テーブル1 [AS エイリアス1] [INNER] JOIN テーブル2 [AS エイリアス2] ON [テーブル1].キー1 = [テーブル2].キー2; |
student_coursesテーブルとstudentsテーブルを結合するクエリーの例。結合の結果、studentsテーブルから学生名を得られる。
|
1 2 3 4 5 6 7 |
SELECT stcs.student_id, st.name, stcs.course_id FROM student_courses AS stcs JOIN students AS st ON stcs.student_id = st.id; |
実行結果。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+------------+--------+-----------+ | student_id | name | course_id | +------------+--------+-----------+ | 1 | 安藤 | 10 | | 1 | 安藤 | 11 | | 3 | 宇藤 | 10 | | 3 | 宇藤 | 11 | | 3 | 宇藤 | 13 | | 4 | 江藤 | 11 | | 4 | 江藤 | 13 | +------------+--------+-----------+ 7 rows in set (0.00 sec) |
3つのテーブルの場合、JOIN ... ON句を並べればよい。以下の例ではcoursesテーブルも結合して、学科名も得ている。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT stcs.student_id, st.name AS student_name, stcs.course_id, cs.name AS course_name FROM student_courses AS stcs JOIN students AS st ON stcs.student_id = st.id JOIN courses AS cs ON stcs.course_id = cs.id; |
実行結果。student_coursesテーブルは学生IDと学科IDしか持っていないが、studentsテーブル、coursesテーブルと結合することで学生名と学科名を使えるようになる。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 3 | 宇藤 | 10 | 数学 | | 1 | 安藤 | 10 | 数学 | | 4 | 江藤 | 11 | 物理学 | | 3 | 宇藤 | 11 | 物理学 | | 1 | 安藤 | 11 | 物理学 | | 4 | 江藤 | 13 | 天文学 | | 3 | 宇藤 | 13 | 天文学 | +------------+--------------+-----------+-------------+ 7 rows in set (0.00 sec) |
LEFT OUTER JOIN
LEFT OUTER JOINは、先に指定されたテーブル(左テーブル)の全レコードに対して、JOINで指定したレコードを対応させる。左テーブルに対応するレコードがないときはNULLになる。LEFT JOINと書いても同じ。
以下のクエリーは、studentsの全学生データに対してstudent_coursesの履修科目データを左結合させる。studentsには登録されているがstudent_coursesにない学生(学科を履修していない学生)についてはNULLになる。
|
1 2 3 4 5 6 7 |
SELECT st.id AS student_id, st.name, stcs.course_id FROM students AS st LEFT JOIN student_courses AS stcs ON st.id = stcs.student_id; |
実行結果は以下の通りで、学生IDの伊藤君はstudent_coursesテーブルに存在しないので、course_idがNULLになっている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+--------+-----------+ | student_id | name | course_id | +------------+--------+-----------+ | 1 | 安藤 | 11 | | 1 | 安藤 | 10 | | 2 | 伊藤 | NULL | | 3 | 宇藤 | 13 | | 3 | 宇藤 | 11 | | 3 | 宇藤 | 10 | | 4 | 江藤 | 13 | | 4 | 江藤 | 11 | +------------+--------+-----------+ 8 rows in set (0.00 sec) |
学科名も欲しい場合は、coursesテーブルを左結合させる。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT st.id AS student_id, st.name AS student_name, cs.id AS course_id, cs.name AS course_name FROM students AS st LEFT JOIN student_courses AS stcs ON st.id = stcs.student_id LEFT JOIN courses AS cs ON stcs.course_id = cs.id; |
実行結果。学科を履修していない伊藤君のデータは、course_nameについてもNULLになる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 1 | 安藤 | 11 | 物理学 | | 1 | 安藤 | 10 | 数学 | | 2 | 伊藤 | NULL | NULL | | 3 | 宇藤 | 13 | 天文学 | | 3 | 宇藤 | 11 | 物理学 | | 3 | 宇藤 | 10 | 数学 | | 4 | 江藤 | 13 | 天文学 | | 4 | 江藤 | 11 | 物理学 | +------------+--------------+-----------+-------------+ 8 rows in set (0.00 sec) |
RIGHT OUTER JOIN
RIGHT JOINは後から指定されたテーブルの全データに対して、先に指定されたテーブルを結合させる。先に指定されたテーブルに対応するレコードがない場合にはNULLになる。RIGHT JOINと書いても同じ。
以下は、student_coursesテーブルのデータにcoursesテーブルのデータを右結合させている。どの学生にも履修されていない学科がある場合はNULLになる。
|
1 2 3 4 5 6 7 |
SELECT stcs.student_id, cs.id AS course_id, cs.name AS course_name FROM student_courses AS stcs RIGHT JOIN courses as cs ON stcs.course_id = cs.id; |
実行結果。化学はどの学生にも履修されていないのでNULLになっている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+-----------+-------------+ | student_id | course_id | course_name | +------------+-----------+-------------+ | 3 | 10 | 数学 | | 1 | 10 | 数学 | | 4 | 11 | 物理学 | | 3 | 11 | 物理学 | | 1 | 11 | 物理学 | | NULL | 12 | 化学 | | 4 | 13 | 天文学 | | 3 | 13 | 天文学 | +------------+-----------+-------------+ 8 rows in set (0.00 sec) |
3つ以上のテーブルでは、最も右側のテーブルをRIGHT JOINを連ねる。最後以外のJOINはRIGHTがなくても同じ結果になる。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT st.id AS student_id, st.name AS student_name, cs.id AS course_id, cs.name AS course_name FROM student_courses AS stcs RIGHT JOIN students AS st ON stcs.student_id = st.id RIGHT JOIN courses as cs ON stcs.course_id = cs.id; |
実行結果。履修されていない化学の学生IDと学生名がNULLになっている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 3 | 宇藤 | 10 | 数学 | | 1 | 安藤 | 10 | 数学 | | 4 | 江藤 | 11 | 物理学 | | 3 | 宇藤 | 11 | 物理学 | | 1 | 安藤 | 11 | 物理学 | | NULL | NULL | 12 | 化学 | | 4 | 江藤 | 13 | 天文学 | | 3 | 宇藤 | 13 | 天文学 | +------------+--------------+-----------+-------------+ 8 rows in set (0.00 sec) |
RIGHT JOINのテーブルを入れ替えることで、LEFT JOINで同じ結果を得られる。履修科目に関する上の例をLEFT JOINで書きなおすと以下の通り。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT st.id AS student_id, st.name AS student_name, cs.id AS course_id, cs.name AS course_name FROM courses AS cs LEFT JOIN student_courses AS stcs ON stcs.course_id = cs.id LEFT JOIN students AS st ON stcs.student_id = st.id; |
実行結果は同じ。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 3 | 宇藤 | 10 | 数学 | | 1 | 安藤 | 10 | 数学 | | 4 | 江藤 | 11 | 物理学 | | 3 | 宇藤 | 11 | 物理学 | | 1 | 安藤 | 11 | 物理学 | | NULL | NULL | 12 | 化学 | | 4 | 江藤 | 13 | 天文学 | | 3 | 宇藤 | 13 | 天文学 | +------------+--------------+-----------+-------------+ 8 rows in set (0.00 sec) |
FULL OUTER JOIN
FULL OUTER JOINは指定されたテーブルの全データを含める。いずれかのデータが存在しない場合はNULLとなる。テーブルのデータ群の和集合に相当する。
MySQLにはFULL JOINが定義されていないので、同等の機能はUNIONで実現する。
- 2つのテーブルの場合には
LEFT JOINとRIGHT JOINの結果をUNION - 結合テーブルがある場合には、それぞれの
LEFT JOINの結果をUNION UNIONの際に、各SELECTのカラムを同じにしておく
以下は、students、coursesに対するstudent_coursesのLEFT JOINの結果をUNIONで結合している。科目を履修していない学生は学科がNULLとなり、履修されていない科目が学生がNULLとなる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT st.id AS student_id, st.name AS student_name, cs.id AS course_id, cs.name AS course_name FROM students AS st LEFT JOIN student_courses AS stcs ON st.id = stcs.student_id LEFT JOIN courses AS cs ON stcs.course_id = cs.id UNION SELECT st.id AS student_id, st.name AS student_name, cs.id AS course_id, cs.name AS course_name FROM courses AS cs LEFT JOIN student_courses AS stcs ON stcs.course_id = cs.id LEFT JOIN students AS st ON stcs.student_id = st.id; |
実行結果。学科を履修していない伊藤君の学科欄はNULLになり、だれにも履修されていない化学は学生欄がNULLになっている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 1 | 安藤 | 11 | 物理学 | | 1 | 安藤 | 10 | 数学 | | 2 | 伊藤 | NULL | NULL | | 3 | 宇藤 | 13 | 天文学 | | 3 | 宇藤 | 11 | 物理学 | | 3 | 宇藤 | 10 | 数学 | | 4 | 江藤 | 13 | 天文学 | | 4 | 江藤 | 11 | 物理学 | | NULL | NULL | 12 | 化学 | +------------+--------------+-----------+-------------+ 9 rows in set (0.00 sec) |
カラム名の指定によって結果が違う
LEFT OUTER JOINのところで、学生IDと学科IDをstudents.id、couses.idのようにそれぞれのテーブルから指定していた。
一方で、同じIDをstudent_courseのstudent_id、couse_idで指定することもできる。こちらで指定すれば、識別のためのエイリアスを定義しなくていいので便利そうだ。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT stcs.student_id, st.name AS student_name, stcs.course_id, cs.name AS course_name FROM courses AS cs LEFT JOIN student_courses AS stcs ON stcs.course_id = cs.id LEFT JOIN students AS st ON stcs.student_id = st.id; |
しかしこれらを使うと、OUTER JOINの際に存在しないIDとして扱われてNULLになってしまう。
上のクエリーを実行した結果が以下で、化学を履修している学生がいないので、student_coursesに該当するデータがなく、student_idとcourse_idがNULLになっている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+------------+--------------+-----------+-------------+ | student_id | student_name | course_id | course_name | +------------+--------------+-----------+-------------+ | 3 | 宇藤 | 10 | 数学 | | 1 | 安藤 | 10 | 数学 | | 4 | 江藤 | 11 | 物理学 | | 3 | 宇藤 | 11 | 物理学 | | 1 | 安藤 | 11 | 物理学 | | NULL | NULL | NULL | 化学 | | 4 | 江藤 | 13 | 天文学 | | 3 | 宇藤 | 13 | 天文学 | +------------+--------------+-----------+-------------+ 8 rows in set (0.00 sec) |
これらを表示させたいときには、LEFT OUTER JOINの例のように、左結合される側(全データが使われる側)のテーブルでカラムを指定するとよい。