結合(Join)

結合はリレーショナルデータベースの基本。いろいろと方法がある。

inner join的なもの

おそらく一番簡単なJOIN

user

id name
1 Admin

article

id user_id title
1 1 test
CREATE TABLE USER (
    id INT PRIMARY KEY
  , name VARCHAR(255)
);
CREATE TABLE article (
    id INT PRIMARY KEY
  , user_id INT NOT NULL
  , title VARCHAR(255)
);
 
INSERT INTO USER VALUES(1, 'admin');
INSERT INTO article VALUES(1, 1, 'test');

このように結合可能

SELECT article.id, user.name, article.title FROM article, user WHERE article.user_id = user.id;

長いので、適当に別名を与える事もできる。これはuserをU、articleをAとする例

SELECT A.id, U.name, A.title FROM article A, USER U WHERE A.user_id = U.id;

*(アスタリスク)を使う方法もあるが、カラムが被っている場合はカンマでやったりと多少癖がありそうなのでここでは割愛。

この書式は何となくWHEREだけで済むためわかりやすいのだけど、DBエンジンによってはやたらとパフォーマンスが落ちる事もあるらしい(sqliteとかがそうだとか)。これと等価なものがINNER JOINであり、以下のように記述できる。

mysql> SELECT article.id, user.name, article.title FROM article INNER JOIN user ON user.id = article.user_id;
+----+-------+-------+
| id | name  | title |
+----+-------+-------+
|  1 | admin | test  |
+----+-------+-------+
1 row in set (0.00 sec)

同様に別名を与える事もできる

SELECT A.id, U.name, A.title FROM article A INNER JOIN USER U ON U.id = A.user_id;

ちょっと構文が長くなるが、結果的にこちらで書いた方が良い事が多々ある気がしないでもない。

もうちょっと踏み込んで

列1つでは解り辛いので、データをもうちょっと増やす

user

id name
1 Admin
2 test1
3 test2

article

id user_id title
1 1 test
2 3 test2
3 1 test3
4 3 test4
5 1 test5
6 0 orphan

mysqlの場合の例

DROP TABLE IF EXISTS USER;
CREATE TABLE USER (
    id INT PRIMARY KEY
  , name VARCHAR(255)
);
DROP TABLE IF EXISTS article;
CREATE TABLE article (
    id INT PRIMARY KEY
  , user_id INT NOT NULL
  , title VARCHAR(255)
);
 
INSERT INTO USER VALUES(1, 'admin');
INSERT INTO USER VALUES(2, 'test1');
INSERT INTO USER VALUES(3, 'test2');
 
INSERT INTO article VALUES(1, 1, 'test');
INSERT INTO article VALUES(2, 3, 'test1');
INSERT INTO article VALUES(3, 1, 'test2');
INSERT INTO article VALUES(4, 3, 'test3');
INSERT INTO article VALUES(5, 1, 'test4');
INSERT INTO article VALUES(6, 0, 'orphan');

ここで先程のINNER JOINをするとこんな感じ

mysql> SELECT A.id, U.name, A.title FROM article A INNER JOIN user U ON U.id = A.user_id;
+----+-------+-------+
| id | name  | title |
+----+-------+-------+
|  1 | admin | test  |
|  2 | test2 | test1 |
|  3 | admin | test2 |
|  4 | test2 | test3 |
|  5 | admin | test4 |
+----+-------+-------+
5 rows in set (0.00 sec)

SQLの構文を見るとなんとなくわかると思うが、article.user_idがuser.idにマッチしなかったものは捨てられる。例えばuser_id = 0のarticleとかは捨てられる、し、user.id = 2のtest1さんはarticleに出てきていないので使われる事すらない。これが原則。

left join的なもの

ともあれ一つ前に書いたINNER JOINをLEFT JOINに置き換えたものを一発投げてみる

mysql> SELECT A.id, U.name, A.title FROM article A LEFT JOIN user U ON U.id = A.user_id;
+----+-------+--------+
| id | name  | title  |
+----+-------+--------+
|  1 | admin | test   |
|  2 | test2 | test1  |
|  3 | admin | test2  |
|  4 | test2 | test3  |
|  5 | admin | test4  |
|  6 | NULL  | orphan |
+----+-------+--------+
6 rows in set (0.00 sec)

このように、LEFT JOINの基本はFROMで指定したテーブルに関してはとにかく取ってくるのが基本になっている。従って条件にマッチしなかったもの、nameにはNULLが入る。INNER JOINはさほど気にする事がないのかもしれないが、JOINする時は基本的に目的のテーブルをどれに設定するかによって結果が変わってくるので注意が必要である。

ではRIGHT JOINはどうだろうか。

mysql> SELECT A.id, U.name, A.title FROM article A RIGHT JOIN user U ON U.id = A.user_id;
+------+-------+-------+
| id   | name  | title |
+------+-------+-------+
|    1 | admin | test  |
|    3 | admin | test2 |
|    5 | admin | test4 |
| NULL | test1 | NULL  |
|    2 | test2 | test1 |
|    4 | test2 | test3 |
+------+-------+-------+
6 rows in set (0.00 sec)

これはかなり独特な結果になっているが、基本的には「user」を元にしたデータになっているのがわかる。admin, test1, test2に対して全ての結果をひっぱってきているが、ユーザidが存在しない「orphan」は捨てられている。場合によっては使えるクエリーではある。

いずれにせよ、目的をしっかり立て、何を先にもってくるかというのがJOINの基本になる。今回はテーブル2つの単純な例だけど、3つ以上の複雑な例になっても同じ。

sql/join.txt · 最終更新: 2012/04/07 10:32 (外部編集)
www.chimeric.de Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0