データベースの検索をする際に複数のテーブルを使ってデータを絞り込みたい場合が出てきます。
そのときに使えるのが副問い合わせとJOINです。
と「副問い合わせとは何か」の記事で書きました。
複数のテーブルを参照する場合、副問い合わせでないと出来ないこともあります。
しかし、大抵の場合はJOINで出来ます。
JOINの方が個人的にはイメージしやすくて簡単です。
JOINとは
Joinとは「参加する」とか「結びつける」という意味です。
つまり複数のテーブルを結びつけて1つのテーブルのように扱える技術です。
JOINには INNER JOIN と LEFT JOIN, RIGHT JOIN があります。
INNER JOIN
[aside type=”normal”]SELECT カラム1, カラム2, ･･･ FROM テーブル1 INNER JOIN テーブル2 ON (結合条件);[/aside]
FROM句の後にテーブルを指定しますが、そこで「テーブル1 INNER JOIN テーブル2」のように何のテーブルが結合するかを指定します。
ONの後の ( ) の中には結合する条件を書きます。
INNER JOIN は、左に書いたテーブル(テーブル1)と右に書いたテーブル(テーブル2)の両方にデータがあるものだけを出力します。
どちらか一方のテーブルのデータ1件に対し、もう一方の関連するデータが複数件ある場合には複数のデータが表示されます。
テーブル1とテーブル2は逆にしても同じ結果になります。
LEFT JOIN
[aside type=”normal”]SELECT カラム1, カラム2, ･･･ FROM テーブル1 LEFT JOIN テーブル2 ON (結合条件);[/aside]
LEFT JOIN は「LEFT OUTER JOIN」と書いても良いです。どちらも同じです。
LEFT JOIN は、左に書いたテーブル(テーブル1)に関連する右のテーブル(テーブル2)のデータが無くても左のテーブルのデータは全て表示されます。
どちらか一方のテーブルのデータ1件に対し、もう一方の関連するデータが複数件ある場合には複数のデータが表示されます。
LEFT JOIN は INNER JOIN と比べると右のテーブルが無くても表示されるというものです。
RIGHT JOIN
[aside type=”normal”]SELECT カラム1, カラム2, ･･･ FROM テーブル1 RIGHT JOIN テーブル2 ON (結合条件);[/aside]
これは LEFT JOIN の逆です。
LEFT JOIN の 左のテーブルと右のテーブルを入れ替えたものと同じ結果が得られます。
僕はあまり使わないです。
JOINのサンプル
サンプルデーは以下のものとします。
ユーザーテーブル
| id | user_name |
|---|---|
| 1 | 田中 |
| 2 | 鈴木 |
| 3 | 山田 |
商品テーブル
| id | product_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | みかん | 50 |
| 3 | マンゴー | 120 |
購入履歴テーブル
| id | user_id | product_id |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 3 | 3 |
| 3 | 1 | 2 |
INNER JOIN
SELECT u.id, u.user_name, h.id, h.user_id, h.product_id FROM user AS u INNER JOIN history AS h ON (u.id = h.user_id);
AS句はテーブルに別名をつける場合に使います。
「user AS u」でuserテーブルを u とし、u.id や u.user_name は userテーブルの id や user_name ということです。
結果は以下のようになります。
| u.id | u.user_name | h.id | h.user_id | h.product_id |
|---|---|---|---|---|
| 3 | 山田 | 1 | 3 | 1 |
| 3 | 山田 | 2 | 3 | 3 |
| 1 | 田中 | 3 | 1 | 2 |
山田さんは2回購入しているのでデータも2件出てきます。
そして、鈴木さんは1度も購入していないのでデータがありません。
購入したことのある人の履歴と購入者の名前を取得したい場合はこれで大丈夫です。
LEFT JOIN
SELECT u.id, u.user_name, h.id, h.user_id, h.product_id FROM user AS u LEFT JOIN history AS h ON (u.id = h.user_id);
結果は以下のようになります。
| u.id | u.user_name | h.id | h.user_id | h.product_id |
|---|---|---|---|---|
| 3 | 山田 | 1 | 3 | 1 |
| 3 | 山田 | 2 | 3 | 3 |
| 1 | 田中 | 3 | 1 | 2 |
| 2 | 鈴木 | NULL | NULL | NULL |
山田さんは2回購入しているのでデータも2件出てくるところは INNER JOIN も同じです。
鈴木さんは1度も購入していなく購入履歴テーブルにデータはありませんがユーザーデータは表示され、購入履歴データはNULLになります。
ただ、僕はこういう使い方はしないです。（そういう使い方知らないだけかもしれませんが）
今回ユーザーテーブルと購入履歴テーブルは １対多 の関係にあります。
1件のユーザーデータに対し、複数の購入履歴データが存在するというものです。
その場合、購入履歴テーブルをベースに購入した人の名前を取得したいパターンの方が自然かと思います。
だとしたらテーブルを入れ替えてこう書きます。
SELECT u.id, u.user_name, h.id, h.user_id, h.product_id FROM history AS h LEFT JOIN user AS u ON (u.id = h.user_id);
結果は INNER JOIN と一緒ですが。
RIGHT JOIN
SELECT u.id, u.user_name, h.id, h.user_id, h.product_id FROM user AS u INNER JOIN history AS h ON (u.id = h.user_id);
結果は以下のようになります。
| u.id | u.user_name | h.id | h.user_id | h.product_id |
|---|---|---|---|---|
| 3 | 山田 | 1 | 3 | 1 |
| 3 | 山田 | 2 | 3 | 3 |
| 1 | 田中 | 3 | 1 | 2 |
LEFT JOINで紹介した2つ目のサンプルと同じ結果になります。
右か左かどちらをベースにするかの違いだからです。
まとめ
テーブルを結合して結果を得るには副問い合わせかJOINがある。
JOIN は INNER JOIN とLEFT JOIN と RIGHT JOIN がある。
これは僕のやり方ですが、基本的には LEFT JOIN しか使わず、1対多 の多の方のテーブルを左にする。
これで大抵うまくいきます。
コメント