00メニュー > 01はじめてのMySQL > 02MySQLの基礎知識 > 03MySQLを使いこなす > 04MySQL+PHPの基礎 > 05MySQL+PHPの実践 > 06Appendix

10複数のテーブルを利用する(備忘録)→ > 01p > 02p > 03p > 04p > 05p > END

10 複数のテーブルを利用する

  単純にINSERT、SELECT、DELETEするだけでデータベースが運用できるわけではありません。実践では複雑に絡み合った複数のテーブルを使って大量のデータを扱い、処理の効率を考え、そして不慮のデータ消滅も許されません。MySQLの勉強も佳境を迎えています。条件を設定した抽出、編集から、リレーショナルの名にふさわしい複数のテーブルの利用。そして「ビュー」「ストアドプロシージャ」「トランザクション」「ファイル操作」という、より実践的で必要とされる内容を解説します。
☆CHAPTER08⇒いろんな条件で抽出する
☆CHAPTER09⇒テーブルを編集する
★CHAPTER10⇒複数のテーブルを利用する
☆CHAPTER11⇒ビューを使いこなす
☆CHAPTER12⇒ストアドプロシージャを使いこなす
☆CHAPTER13⇒トランザクションを使いこなす
☆CHAPTER14⇒ファイルを使ったやり取り

★CHAPTER10⇒複数のテーブルを利用する(p181)

  ここでは複数のテーブルを扱う方法を紹介します。そもそもリレーショナルデータベースというのは、「リレーショナル」(Relational:関連のある)なところが特徴です。インターネット上の通販サイトなどで実際に稼動しているMySQLデータベースでは、恐ろしいほどたくさんのテーブルが絡み合っています。ここでは、絡み合ったテーブルを結合し、組み合わせて使うテクニックを学びます。

(1)《テーブルのカラム構造やデータのコピー》

(1)《複数のテーブルのレコードを合わせて表示する》p181

(1-1)<複数のテーブルの利用で使うサンプル確認>

次は「社員情報テーブルtd1」「売上情報テーブルtb」、そして新たに登場する「別の営業所の社員情報テーブルtb2」及び「社員出身県情報テーブルtb3」です。 あらかじめ「tb2」「tb3」の準備をしておきます。

●各テーブルが次のようになっているものとして解説 p181

●tb(売上情報テーブル)


mysql> SELECT * FROM tb;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+------+------+------+
10 rows in set (0.00 sec)

●tb1(社員情報テーブル)


mysql> SELECT * FROM tb1;
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高橋 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡辺 | 23 |
| A105 | 西沢 | 35 |
+------+------+------+
5 rows in set (0.00 sec)

●tb2(別の営業所の社員情報テーブル)


mysql> SELECT * FROM tb2;
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A106 | 中村 | 26 |
| A107 | 田中 | 24 |
| A108 | 鈴木 | 23 |
| A109 | 村井 | 25 |
| A110 | 吉田 | 27 |
+------+------+------+
5 rows in set (0.00 sec)

●tb3(社員出身県情報テーブル)


mysql> SELECT * FROM tb3;
+------+----------+
| bang | ken |
+------+----------+
| A101 | 東京都 |
| A102 | 埼玉県 |
| A103 | 神奈川県 |
| A104 | 北海道 |
| A105 | 静岡県 |
+------+----------+
5 rows in set (0.00 sec)

(1-2)<複数の抽出結果を合わせて表示 p182>

複数のテーブルからデータを取り出し、寄せ集めて表示してみましよう。単純に複数のテーブルから抽出したデータを集めるときには、「UNION」を使います。 たとえば、「昨年の顧客テーブルから得意先を抽出、さらに「今年の顧客テーブルから抽出して両方を一度に表示する」というような使い方をします。構造が異なるテーブルからデータを集めることができます。いっしょに表示するカラムのデータ型は、基本的に一致している必要があります。ただし、実際にはデータ型が異なっても、MySQLは「集められるものは集めてしまう」という、大らかな対応をすることが多い。

◇図10-1 UNION

図省略 ※構文は簡単です。通常の「SELECT カラム名 FROM テーブル名」を「UNION」で結ぶだけです。

●書式⇒2つのテーブルのレコードを集めて表示

SELECT カラム名1 FROM テーブル2 UNION SELECT カラム名2 FROM テーブル名2;
※一行で書くとわかりにくいので、次のように記述。

●書式⇒2つのテーブルのレコードを集めて表示(改行+インデント)

SELECT
  カラム名1
FROM
  テーブル名1
UNION
  SELECT
    カラム名2
  FROM
    テーブル名2;
※「SELECT~FROM~」という2つの結果を、「UNION」で集めているわけです。 まずは、単純に、同じカラム構造をしたテーブル「tb1」とテーブル「tb2」のレコードを集めることにしましょう。すべてのカラムを集めるので「SELECT * FROM~」を「UNION」でつないで記述してください。

●実行される内容 p183

●操作手順

①次を実行する
SELECT *
FROM tb1
UNION
SELECT *
FROM tb2;

□【実行結果】

mysql> SELECT *
-> FROM tb1
-> UNION
-> SELECT *
-> FROM tb2;
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高橋 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡辺 | 23 |
| A105 | 西沢 | 35 |
| A106 | 中村 | 26 |
| A107 | 田中 | 24 |
| A108 | 鈴木 | 23 |
| A109 | 村井 | 25 |
| A110 | 吉田 | 27 |
+------+------+------+
10 rows in set (0.02 sec)

mysql> (SELECT * FROM tb1) UNION (SELECT * FROM tb2);
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高橋 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡辺 | 23 |
| A105 | 西沢 | 35 |
| A106 | 中村 | 26 |
| A107 | 田中 | 24 |
| A108 | 鈴木 | 23 |
| A109 | 村井 | 25 |
| A110 | 吉田 | 27 |
+------+------+------+
10 rows in set (0.00 sec)

mysql>

※見やすくするために、それぞれの「SELECT~」を()で囲んで記述することもあります。
(SELECT * FROM tb1) UNION (SELECT * FROM tb2);

(1-3)<3つ以上のテーブルから「UNION」で集める>p184

前項では「bang」「A102」「A104」「A107」のレコードをわざわざ1つずつ「SELECT」して、「UNION」で集めた例です。

(SELECT * FROM tb WHERE bang='A102')
UNION
(SELECT * FROM tb WHERE bang='A103')
UNION
(SELECT * FROM tb WHERE bang='A104')
UNION
(SELECT * FROM tb WHERE bang='A107');
結局「A101」以外を抽出しているので

SELECT *
FROM tb
WHERE bang
NOT IN('A101');
③あるいは
SELECT *
FROM tb
WHERE bang
IN('A102','A103','A104','A107');
を実行すると同じ結果になります。
なお、表示されるレコードは同じですが、順番は異なるので注意。

(1-4)<条件を付けて複数の抽出結果を合わせて表示する>p185

「UNION」で複数のレコードを「SELECT」するときには条件を付けて集めるには、その命令の最後に「WHERE 条件」を付けます。
次に2種類の抽出をして、その社員番号(bang)を集めてみます。
①テーブル「tb」売上(uria)が200以上のレコードを持つ社員番号(bang)
②テーブル「tb1」で年齢(tosi)が35以上のレコードを持つ社員番号(bang)

●実行される内容 p186

●操作手順

①次を実行する
(SELECT
bang
FROM
tb
WHERE uria>=200)

UNION
(SELECT
bang
FROM
tb1
WHERE tosi>=35);

□【実行結果】

mysql> (SELECT
-> bang
-> FROM
-> tb
-> WHERE uria>=200)
->
-> UNION
-> (SELECT
-> bang
-> FROM
-> tb1
-> WHERE tosi>=35);
+------+
| bang |
+------+
| A101 |
| A102 |
| A105 |
+------+
3 rows in set (0.61 sec)

mysql>

(1-5)<複数の抽出をあわせて表示する(重複を許容)>

前項では、整数と1人ずつの社員番号と氏名が表示されました。 この結果は、複数のテーブルからレコードを集めています。つまり①で「A101」「A102」が抽出され②からは「A101」「A105」が抽出されているのです。 ところが「A101」は1レコードしか表示されません。これは、抽出するときに「重複しているデータを省く」という作業も合わせて実行されているのです。 ※大量のデータでこの「重複を省く」作業が入ると、無視できない待ち時間が生じます。従って、このような場合には、「UNION」にALLを付けます。 ※前項のUNIONの部分を「UNION ALL」とすると、次のようになります。

●操作手順

①次を実行します
(SELECT bang FROM tb WHERE uria>=200)
UNION
(SELECT bang FROM tb1 WHERE tosi>=35);

□【実行結果】

mysql> (SELECT bang FROM tb WHERE uria>=200)
-> UNION
-> (SELECT bang FROM tb1 WHERE tosi>=35);
+------+
| bang |
+------+
| A101 |
| A102 |
| A105 |
+------+
3 rows in set (0.48 sec)

mysql>
※「A101」は両方の処理により、2件表示されます。

(2)<複数のテーブルを結合して表示する(内部結合)>

前項の「UNION」は、単純に複数のテーブルのレコードを合わせるものでした。ここでは内部結合「JOIN」について解説します。

(2-1)<別のテーブルのデータも使って処理する>

複数のテーブルを何かのキーで結びつけて処理するのが「結合」です。ここまで、テーブル「tb」と「tb1」を扱ってきました。会社の「売上情報」と「社員情報」を、分散して管理しているモデルです。 テーブル「tb」で売上の処理を行なって、職員の氏名は必要なときにテーブル「tb1」から引っ張ってくることができます。

●テーブル p188

※さて、テーブル「tb」の「bang」が「A101」なのは年齢40の「佐藤」さんですが、テーブル「tb」だけだと社員の氏名がわかりません。そこで、売上情報テーブル「tb」と社員情報テーブル「tb1」を、両方に共通の社員番号のカラム「bang」をキーにして結合して表示してみましょう。 ※2つのテーブルを結合して使うには、JOINを使います。

●書式⇒2つのテーブルを結合する

SELECT カラム名
FROM テーブル1
JOIN 結合するテーブル2
ON テーブル1のカラム=テーブル2のカラム;

※ON~、2つのテーブルを結び付るキーとなるカラムの条件を書きます。たとえば、テーブル「tb」とテーブル「tb1」なら、カラム「bang」が共通の値を持っているので、これを設定します。次のようになります。
ON tb.bang=tb1.bang
では、具体的に処理してみましょう。表示するカラムは選択できますが、取り敢えずは「*」で全部表示してみましょう。

●実行される内容 p189

●操作手順

①次を実行する
SELECT *
FROM tb
JOIN tb1
ON tb.bang=tb1.bang;

□【実行結果】

mysql> SELECT *
-> FROM tb
-> JOIN tb1
-> ON tb.bang=tb1.bang;
+------+------+------+------+------+------+
| bang | uria | tuki | bang | nama | tosi |
+------+------+------+------+------+------+
| A103 | 101 | 4 | A103 | 中川 | 20 |
| A102 | 54 | 5 | A102 | 高橋 | 28 |
| A104 | 181 | 4 | A104 | 渡辺 | 23 |
| A101 | 184 | 4 | A101 | 佐藤 | 40 |
| A103 | 17 | 5 | A103 | 中川 | 20 |
| A101 | 300 | 5 | A101 | 佐藤 | 40 |
| A102 | 205 | 6 | A102 | 高橋 | 28 |
| A104 | 93 | 5 | A104 | 渡辺 | 23 |
| A103 | 12 | 6 | A103 | 中川 | 20 |
+------+------+------+------+------+------+
9 rows in set (0.00 sec)

mysql>

●内部結合

結果を見ると、テーブル「tb1」から「bang」が一致するレコードを選び出し、結合して表示しています。(「A107」のレコードが表示されない理由は前項参照)。 このように一致するレコードを取り出すような結合を、内部結合といいます。「内部結合」であることをはっきりと示すときは、「JOIN」の部分を「INNER JOIN」と記述することもできます。次のように「INNER」を付けても、結果は同じ。

SELECT * FROM tb INNER JOIN tb1 ON tb.bang=tb1.bang;

◇図10-2 内部結合 p191

図省略

※「内部結合」に対して、一方のテーブルにあるレコードは一致していなくてもすべて取り出すことを「外部結合」といいます。

(2-2)<カラムを選んで表示するp191>

前項では、「*」でカラムのすべてを表示しました。今度は、表示するカラムを選ぶこととしましょう。この場合、カラムの記述は「テーブル名.カラム名」とします。 たとえばテーブル「tb」のカラム「uria」なら「tb.uria」となります。表示する順番も指定した通りになります。同じカラムを何回表示してもかまいません。たとえば、
SELECT tb.bang,tb1.nama,tb.uria
FROM tb
JOIN tb1
ON tb.bang=tb1.bang;

□【実行結果】

mysql> SELECT tb.bang,tb1.nama,tb.uria
-> FROM tb
-> JOIN tb1
-> ON tb.bang=tb1.bang;
+------+------+------+
| bang | nama | uria |
+------+------+------+
| A103 | 中川 | 101 |
| A102 | 高橋 | 54 |
| A104 | 渡辺 | 181 |
| A101 | 佐藤 | 184 |
| A103 | 中川 | 17 |
| A101 | 佐藤 | 300 |
| A102 | 高橋 | 205 |
| A104 | 渡辺 | 93 |
| A103 | 中川 | 12 |
+------+------+------+
9 rows in set (0.00 sec)

mysql>

(2-3)<テーブル名にエイリアスを使う>

エイリアスは、テーブル名に設定することもできます。方法はカラムに付けるときと同じ。

●書式⇒テーブルにエイリアスを付ける

テーブル名 AS エイリアス

※たとえば次は、テーブル「tb」に「x」というエイリアスを付けた例です。
SELECT * FROM tb AS x ;

※テーブルにエイリアスを付けるメリットは、テーブルを結合するときに現わします。 実際に使われるテーブルの多くは、「Kig_uriage2008_2」のような複雑なものです。
複数のテーブルを結合する場合、必ず「正式なテーブル名.カラム名」という記述が必要です。 しかし、「Kig.uriage2008_2.uria」は、「x.uria」等で記述します。
それでは、売上情報テーブル「tb」の社員番号と売上、そして社員情報テーブル「tb1」の名前を選んで、わかりやすくエイリアスも付けて表示してみます。
テーブル「tb」の「bang」、「tb1」のカラム「bang」が一致するレコードを結合し、「tb」の「bang」、「tb1」の「nama」、「tb」の「uria」のカラムを付けて実行しましょう。

●実行される内容 p193

●操作手順

①次を実行する
SELECT x.bang, y.nama, x.uria
FROM tb as x
JOIN tb1 as y ON x.bang=y.bang;

□【実行結果】

mysql> SELECT x.bang, y.nama, x.uria
-> FROM tb as x
-> JOIN tb1 as y ON x.bang=y.bang;
+------+------+------+
| bang | nama | uria |
+------+------+------+
| A103 | 中川 | 101 |
| A102 | 高橋 | 54 |
| A104 | 渡辺 | 181 |
| A101 | 佐藤 | 184 |
| A103 | 中川 | 17 |
| A101 | 佐藤 | 300 |
| A102 | 高橋 | 205 |
| A104 | 渡辺 | 93 |
| A103 | 中川 | 12 |
+------+------+------+
9 rows in set (0.00 sec)

mysql>

(2-4)<USINGを使ってON~の部分を見やすくするp194>

ここまでの例では、社員番号のカラム「bang」を結合の条件として「tb.babg=tb1.bang」を指定しました。キーが両方とも「bang」というカラムでした。
ここで、カラムが同じである必要はありません。キーとなる内容さえ同じであれば、例えば「tb1」の「bang」のカラム名が「bangou」であってかまわないのです。
この場合、単に「ON tb.bang=tb1.bangou」となるだけです。
この例では、たまたま「bang」という同じカラム名になっています。このように、同じカラム名で指定する場合は、USING(キーとなるカラム名)と簡単に記述することができます。
※これを使うと、次のように見やすくなります。
SELECT tb.bang,tb1.nama,tb.uria
FROM tb
JOIN tb1
JOIN tb1
USING(bang);

(2-5)<結合したテーブルから「WHERE」で設定した条件で抽出するp195>

結合したテーブルから「WHERE」で条件を設定して、該当したデータだけを表示してみよう。最後に「WHERE」の条件を付けるだけです。カラム名は必ず「テーブル名.カラム名」のようにテーブル名を付けて記述します。
もし、テーブルにエイリアスが設定されている場合は「エイリアス.カラム名」と記述することもできます。
売上情報テーブル「tb」で、100以上の優秀な売上だけに限定して、社員情報テーブル「tb1」の氏名と合わせて表示します。さらに、表示する項目にもエイリアスを付けでみます。
※具体的には、テーブル「tb」にテーブル「tb1」を結合し、テーブル「tb」のカラム「uria」の値が100以上のレコードを表示します。表示するにはカラム「bang」「nama」「uria」で、それぞれに「社員情報」「氏名」「売上」のエイリアスをエイリアスを付けましょう。

●実行される内容 p195

●操作手順

SELECT tb.bang AS '社員番号',tb1.nama AS '氏名',tb.uria AS '売上'
FROM tb
JOIN tb1
USING(bang)
WHERE tb.uria >= 100;

□【実行結果】

mysql> SELECT tb.bang AS '社員番号',tb1.nama AS '氏名',tb.uria AS '売上'
-> FROM tb
-> JOIN tb1
-> USING(bang)
-> WHERE tb.uria >= 100;
+----------+------+------+
| 社員番号 | 氏名 | 売上 |
+----------+------+------+
| A103 | 中川 | 101 |
| A104 | 渡辺 | 181 |
| A101 | 佐藤 | 184 |
| A101 | 佐藤 | 300 |
| A102 | 高橋 | 205 |
+----------+------+------+
5 rows in set (0.00 sec)

mysql>

(2-6)<たくさんのテーブルのデータを使って抽出する>

「JOIN」するテーブルは1つだけ、という制限はありません。2つでも3つでも結合することが可能です。「SELECT~JOIN~ON~」でさらに「JOIN~ON~」を記述して結合していきます。ただし、たくさんのテーブルを結合すればそれだけ処理に時間がかかり、記述も難関になります。2つ以上のテーブルを内部結合する場合は、次のようにします。

●書式⇒複数のテーブルを内部結合する

SELECT ~ FROM
テーブル名1
JOIN テーブル名2 結合条件
JOIN テーブル名3 結合条件
....
....
;
※ここで、D株式会社の社員の「出身県」の情報が、次のようなテーブル「tb3」にあるとします。テーブル「tb3」は次のように、社員番号「bang」と出身都道府県を表すカラム「ken」から構成されます。
このテーブル「tb3」を用意してください。

テーブル「tb3」 p197

※社員番号「A101」の「佐藤」さんは「東京都」出身だったわけです。 テーブル「tb」「tb1」「tb3」には、共通の値を持ったカラム「bang」があります。 では、カラム「bang」をキーにして、売上情報テーブル「tb」に社員情報テーブル「tb1」と社員出身県テーブル「tb3」を結合し、「社員番号」(テーブル「tb」の「bang」)、「出身県」(テーブル「tb3」の「ken」)の3つを表示してみましょう。

●実行される内容 p198

●操作手順

①次を実行する
SELECT
tb.bang,tb.uria,tb1.nama,tb3.ken
FROM
tb
JOIN
tb1
USING(bang)
JOIN
tb3
USING(bang)
;

□【実行結果】

mysql> SELECT
-> tb.bang,tb.uria,tb1.nama,tb3.ken
-> FROM
-> tb
-> JOIN
-> tb1
-> USING(bang)
-> JOIN
-> tb3
-> USING(bang)
-> ;
+------+------+------+----------+
| bang | uria | nama | ken |
+------+------+------+----------+
| A103 | 101 | 中川 | 神奈川県 |
| A102 | 54 | 高橋 | 埼玉県 |
| A104 | 181 | 渡辺 | 北海道 |
| A101 | 184 | 佐藤 | 東京都 |
| A103 | 17 | 中川 | 神奈川県 |
| A101 | 300 | 佐藤 | 東京都 |
| A102 | 205 | 高橋 | 埼玉県 |
| A104 | 93 | 渡辺 | 北海道 |
| A103 | 12 | 中川 | 神奈川県 |
+------+------+------+----------+
9 rows in set (0.08 sec)

mysql>

※結合の条件には、共通の「bang」を使って「USING(bang)」としましたが、「ON」を使って
「ON tb.bang=tb1.bang」
「ON tb.bang=tb3.bang」とすると。次のようになります。

SELECT
tb.bang,tb.uria,tb1.nama,tb3.ken
FROM
tb
JOIN
tb1
ON tb.bang=tb1.bang
JOIN
tb3
ON tb.bang=tb3.bang
;

(3)<複数のテーブルのすべてのレコードを表示する(外部結合)p200>

(3-1)<外部結合とは>

さて、ここまで2つのテーブル「tb」と「tb1」を中心に使って結合の説明してきました。

●もう一度テーブルの内容を確認してみます。 p200

※前項では、社員番号「bang」をキーにして、この2つのテーブルを「内部結合」しました。その時に、気になるのが1つ残っていました。実はテーブル「tb」には1人、テーブル「tb1」の社員一覧に載っていない人物「bang=A107」さんがいます。 以下は、前項の結果です。内容をもう一度確認してみましょう。

SELECT x.bang,y.nama,x.uria
FROM tb as x
JOIN tb1 as y
ON x.bang=y.bang;

●tb(売上テーブル) p200_2

※実は「A107」は別の営業所の社員なので、テーブル「tb1」にはデータはありません。
そのため、「JOIN」で結合してもそのレコードは表示されません。さらに、「tb1」の社員名簿には「A105」の西沢さんの情報がありますが、売上一覧にその情報がありません。
つまり、名簿には記載されているものの、売上がないということらしく、「A105」のデータも表示されないのです。
でも、「レコードがあるんだから表示させなきゃ困る!」ということもあります。 このような場合には、外部結合を使います。外部結合は、以下の特徴があります。 「外部結合」は、一致していなくても、一方のテーブルのレコードはすべて取り出す。

(3-2)<外部結合の種類>

外部結合には、結合するときにどちらのテーブルのレコードをすべて書き出すかによって、 以下の2つの種類があります。
①左外部結合(LEFT JOIN)
「一致したレコード」および「テーブル1(左側に相当)の全データ」を表示します。 ②右外部結合(RIGHT JOIN)
「一致したレコード」および「結合するテーブル2(右側に相当)の全データ」を表示します。
【図10-3 左外部結合(LEFT JOIN)省略】
【図10-4 右外部結合(RIGHT JOIN)省略】

(3-3)<左側外部結合を使うp202>

売上情報テーブル「tb」に売上実績があるのに、社員情報テーブル「tb1」に載っていない「A107」さんのレコードを含めて表示してみましょう。 ※左外部結合にするときには、内部結合での記述「JOIN」を「LEFT JOIN」に変えるだけです。

●書式⇒左外部結合

SELECT カラム名
FROM テーブル1
LEFT JOIN 結合するテーブル名2
ON テーブル1のカラム=テーブル2のカラム;

※「bang」をきーにして、テーブル「tb」と「tb1」で一致したレコードと、テーブル「tb」のすべてのレコードを「左外部結合」で表示しましょう。ただし、表示するテーブル「tb」の「bang」と「tb1」の「nama」です。

●実行される内容 p203

画像】

●操作手順

①次を実行する
SELECT
tb.bang,tb1.nama
FROM
tb
LEFT JOIN
tb1
USING(bang)
;

□【実行結果】

mysql> SELECT
-> tb.bang,tb1.nama
-> FROM
-> tb
-> LEFT JOIN
-> tb1
-> USING(bang)
-> ;
+------+------+
| bang | nama |
+------+------+
| A103 | 中川 |
| A102 | 高橋 |
| A104 | 渡辺 |
| A101 | 佐藤 |
| A103 | 中川 |
| A101 | 佐藤 |
| A102 | 高橋 |
| A104 | 渡辺 |
| A103 | 中川 |
| A107 | NULL |
+------+------+
10 rows in set (0.00 sec)

mysql>

※左側に相当するテーブル「tb」だけにある「A107」さんのレコードが表示されます。
そして、右側に相当するテーブル「tb1」だけにある「A105」の「西沢」さんのデータは表示されません。

(3-4)<右外部結合を使うp205>

今度は逆に、「一致したレコード」と「結合された右側テーブルの全データ」を表示する「右外部結合」の例です。
単に、RIHT? JOINを返すだけでOKです。

●書式⇒右外部結合

SELECT カラム名
FROM テーブル1
RIGHT JOIN 結合するテーブル2
ON テーブル1のカラム=テーブル2のカラム;

●実行される内容 p205

画像】

●操作手順

①次を実行する
SELECT
tb.bang,tb1.nama
FROM
tb
RIGHT JOIN
tb1
USING(bang)
;

□【実行結果】

mysql> SELECT
-> tb.bang,tb1.nama
-> FROM
-> tb
-> RIGHT JOIN
-> tb1
-> USING(bang)
-> ;
+------+------+
| bang | nama |
+------+------+
| A101 | 佐藤 |
| A101 | 佐藤 |
| A102 | 高橋 |
| A102 | 高橋 |
| A103 | 中川 |
| A103 | 中川 |
| A103 | 中川 |
| A104 | 渡辺 |
| A104 | 渡辺 |
| NULL | 西沢 |
+------+------+
10 rows in set (0.00 sec)

mysql>

(3-5)<(注意)左外部結合と右外部結合の混在は避ける>

外部結合を使う場合、重要な注意点があります。同じデータベースの中で、左外部結合と左外部結合を混在することもできます。また、右か左を選ぶ代わりに、結合するテーブルを逆にすることも可能です。 ※しかし、左外部結合と右外部結合の両方が存在すると、のちのちに間違いを引き起こす原因になりかねません。従って、両方を混在させるべきでない。

(4)自己結合

(4-1)自己結合とは

テーブルは自分自身、つまり同じ名前のテーブルを結合することができます。 これを自己結合といいます。テーブルに同じ名前のテーブルを結合することで、単純に実行すると同じカラム名が2つ表示されてしまいます。これはカラムが識別できない(エラーになる)ので、結合するときには必ずエイリアスを付けます。

●書式⇒自己結合

SELECT カラム名 FROM テーブル名 as エイリアス1 JOIN テーブル名 AS エイリアス2;

※たった1つの同じテーブルに、2つのエイリアス(別名)を付けます。テーブルは1つなのに、名前だけ2つある状態です。これを実行すると、大変なことになります。社員情報テーブル「tb1」を自己結合して、すべてのカラムを表示してみましょう。

●実行される内容 p208

画像】

●操作手順

SELECT *
FROM tb1
AS a
JOIN tb2
AS b;

【実行結果】

mysql> SELECT *
-> FROM tb1
-> AS a
-> JOIN tb2
-> AS b;
+------+------+------+------+------+------+
| bang | nama | tosi | bang | nama | tosi |
+------+------+------+------+------+------+
| A101 | 佐藤 | 40 | A106 | 中村 | 26 |
| A102 | 高橋 | 28 | A106 | 中村 | 26 |
| A103 | 中川 | 20 | A106 | 中村 | 26 |
| A104 | 渡辺 | 23 | A106 | 中村 | 26 |
| A105 | 西沢 | 35 | A106 | 中村 | 26 |
| A101 | 佐藤 | 40 | A107 | 田中 | 24 |
| A102 | 高橋 | 28 | A107 | 田中 | 24 |
| A103 | 中川 | 20 | A107 | 田中 | 24 |
| A104 | 渡辺 | 23 | A107 | 田中 | 24 |
| A105 | 西沢 | 35 | A107 | 田中 | 24 |
| A101 | 佐藤 | 40 | A108 | 鈴木 | 23 |
| A102 | 高橋 | 28 | A108 | 鈴木 | 23 |
| A103 | 中川 | 20 | A108 | 鈴木 | 23 |
| A104 | 渡辺 | 23 | A108 | 鈴木 | 23 |
| A105 | 西沢 | 35 | A108 | 鈴木 | 23 |
| A101 | 佐藤 | 40 | A109 | 村井 | 25 |
| A102 | 高橋 | 28 | A109 | 村井 | 25 |
| A103 | 中川 | 20 | A109 | 村井 | 25 |
| A104 | 渡辺 | 23 | A109 | 村井 | 25 |
| A105 | 西沢 | 35 | A109 | 村井 | 25 |
| A101 | 佐藤 | 40 | A110 | 吉田 | 27 |
| A102 | 高橋 | 28 | A110 | 吉田 | 27 |
| A103 | 中川 | 20 | A110 | 吉田 | 27 |
| A104 | 渡辺 | 23 | A110 | 吉田 | 27 |
| A105 | 西沢 | 35 | A110 | 吉田 | 27 |
+------+------+------+------+------+------+
25 rows in set (0.09 sec)

mysql>

「佐藤」さんのレコードに「佐藤」「高橋」「中川」「渡辺」「西沢」が付き、さらに「高橋」さんに「佐藤」「高橋」「中川」「渡辺」「西沢」がくっ付くと、レコード数×レコード数という、恐ろしい数が結合してしまいます。 ※この例では5レコードなので5×5=25ですが、もし1000件を超えるようなレコードだったら?・・・と思うと、自己結合なんて無謀なことのように思えてきます。 ※でも、決して無謀ではありません、データベースの世界では、こうした力技のような手法が、当たり前のように使われているのです。

(4-2)<順位付けの妙技 その1>

テーブルに、まったく同じテーブルを結合させることで、いったい何が出来るのでしょうか? ※少なくても自己結合させた結果には、すべての組合せが存在します。ですから、求める組合せがそこに存在するのでしたら、後は条件を設定して必要なものを選べばよいのです。 ※データベースにおける順位は、「ORDER」や「GROUP」などを組み合わせて処理をしないと出せない、やっかいな存在です。

●自己結合の確認

ここでは、社員情報テーブル「tb1」で年齢「tosi」の大きいものからの順位を付けてみることにしましょう。
【●テーブル「tb1」を「自己結合」で表示(抜粋)省略】
※たとえば右側の「佐藤40」に対して、左側に「佐藤40」「高橋28」「中川20」「渡辺23」「西沢35」のテーブルが並んでいる5行に注目してください。この5行の中で、右側の「佐藤40」の40以上である左側の数値は40のたった1つです。「40以上」というのは40も含みますから。佐藤さんの順位は1番です。
つまり、「自己結合」して、左側の「tosi」が右側の「tosi」以上になっている行の数を「bang」のグループごとに数えればよいことになります。
数は「COUNT(*)」で数えます。SQL文を考えてみましょう。

●テーブル構造 p212

●操作手順

①次を実行する
SELECT a.nama,a.tosi,COUNT(*)
FROM tb1 AS a
JOIN tb1 AS b
WHERE a.tosi<=b.tosi
GROUP BY a.bang;

□【実行結果】

mysql> SELECT a.nama,a.tosi,COUNT(*)
-> FROM tb1 AS a
-> JOIN tb1 AS b
-> WHERE a.tosi<=b.tosi
-> GROUP BY a.bang;
+------+------+----------+
| nama | tosi | COUNT(*) |
+------+------+----------+
| 佐藤 | 40 | 1 |
| 高橋 | 28 | 3 |
| 中川 | 20 | 5 |
| 渡辺 | 23 | 4 |
| 西沢 | 35 | 2 |
+------+------+----------+
5 rows in set (0.00 sec)

mysql>

※まず、自己結合なので基本は「SELECT * FROM tb1 AS a.JOIN tb1 b;」です。 これはWHEREの条件「a.tosi<=b.tosi」を設定します。すると「a.bang」のグループごとに、「a.tosi」以上の「b.tosi」であるレコードが抽出され、この個数を「COUNT(*)」で数えれば順位になるということです。

(5)《SELECTしたレコードからSELECTする(サブクエリ)p213》

(5-1)サブクエリを使えるバージョン

サブクエリを使えるのはMySQLのバージョン4.1以降です。

(5-2)サブクエリとは

サブクエリは「副問い合わせ」ともいいます。サブクエリを使うと、「クエリを実行して取り出されたデータを使って、さらに、クエリを発行する」という、2段階の処理が可能になります。※例えば1段階で「売上テーブルで売上200以上という社員番号を抽出」し、さらに2段階目で「抽出された社員番号に該当する氏名を抽出する」というような処理です。1段階目のクエリを「サブクエリ」と呼んでいます。 1段階目のサブクエリが返すのは、値であったり、カラムであったり、レコードであったりさまざまです。
【図10-5 サブクエリ 省略】

(5-3)<最大の値を持つものを表示 --値を返すサブクエリ その1-->

たとえば次のようなケースは、いったいどうやって処理したらよいのでしょうか?
●テーブル「tb」で、カラム「uria」が最大値であるレコードを表示する
「売上情報テーブルtbで、最も売上の高い(uriaが最大)の社員データを表示」しろ、 という問題です。次のテーブル「tb」の構造を見て、ちょっと考えてみましょう。

●テーブル「tb」 p214

【図10-6 サブクエリの流れ 省略】
※GROUP BY関数
MAXやAVG、SUMなどの集計を行なう関数は、「GROUP BY関数」とも呼ばれ、本来はグループ化したときの値を処理するものです。ただし、「GROUP BY…」がない場合は、テーブル全体を1つのグループとして処理が行なわれます。

●実行される内容 p216

画像の誤り

●操作手順

①次を実行する
SELECT *
FROM tb
WHERE uria
IN (SELECT MAX(uria) FROM tb);

□【実行結果】

mysql> SELECT *
-> FROM tb
-> WHERE uria
-> IN (SELECT MAX(uria) FROM tb);
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A101 | 300 | 5 |
+------+------+------+
1 row in set (0.00 sec)

mysql>

(5-4)<平均以上のレコードを抽出するーー値を返すサブクエリ その2-->

前項でMAX関数を使ったサブクエリを紹介しました。もう1つ、関数を使った例を考えてみましょう。社員情報テーブル「tb1」に登録されている社員の平均年齢を出し、平均年齢以上の1段階目では、テーブル「tb1」でのカラム「tosi」の平均を「AVG」関数を使って出します。 さらに、2段階目で、この平均値以上の「tosi」であるレコードを抽出することになります。 それでは、テーブル「tb1」での年齢(tosi)の平均値を出し、この値以上の年齢(tosi)のレコードを表示してみましょう。

●実行される内容 p217

●操作手順

①次を実行する
SELECT *
FROM tb1
WHERE tosi
>=(SELECT AVG(tosi) FROM tb1);

□【実行結果】

mysql> SELECT *
-> FROM tb1
-> WHERE tosi
-> >=(SELECT AVG(tosi) FROM tb1);
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A105 | 西沢 | 35 |
+------+------+------+
2 rows in set (1.35 sec)

mysql>

(5-5)<INを使う --カラムを返すサブクエリーー>

今度は、サブクエリがカラムを返す例です。1段階目のサブクエリで条件に一致したカラムを返し、そのカラムを含むものを2段階目で抽出します。この場合、次のように「IN」を使います。

●書式⇒サブクエリの構文

SELECT 表示するカラム FROM テーブル名 WHERE カラム名 IN (SELECTによるサブクエリでカラムを抽出);
※1段階目のクエリ(サブクエリ)を、必ず()で囲んで記述します。1段階目の抽出を行い、その値をカラムを含む(IN)ことを条件として、2段階目の抽出を実行します。
※サブクエリを使って、テーブル「tb」で売上(uria)が200以上の「bang」を抽出し、テーブル「tb1」でその該当するレコードを表示してみましょう。

●実行される内容 p219

画像】

●操作手順

SELECT *
FROM tb1
WHERE bang
IN(SELECT bang FROM tb WHERE uria>=200);

□【実行結果】

mysql> SELECT *
-> FROM tb1
-> WHERE bang
-> IN(SELECT bang FROM tb WHERE uria>=200);
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高橋 | 28 |
+------+------+------+
2 rows in set (0.00 sec)

mysql>

(5-6)<INの代わりに「=」を使うとエラーになる?>

※省略

(5-7)<EXISTSで、存在するレコードだけを対象にするp223>

前項で学んだサブクエリは、「WHERE~IN(SELECT・・・)」のようなタイプでした。 これは、1段階目のサブクエリが該当するカラムのデータを返すものです。たとえば、「A101,A102のカラム」というカラムのデータが返され、その値を含むレコードを対象に抽出が行なわれました。※これに対して、特定のカラムはなく1段階目のサブクエリが「対象となるレコードが存在する」という情報を返すのがEXISTSです。 ※実例を見てみることにしましょう。社員情報テーブル「tb1」には、売上情報テーブル「tb」にその社員番号がないレコードも含まれています。これは「A105」のレコードです。

●実行される内容 p223

※つまり、「A105」の西沢さんは、売上がないということです。では、「売上のある社員の、社員情報テーブルのレコード」を、サブクエリを使って表示してみましょう。具体的には、次のように処理します。
(テーブル「tb」に存在するレコードを抽出し、テーブル「tb1」から該当するレコードを表示)
※ます、社員情報テーブル「tb1」のレコードを表示ということなので、
□SELECT * FROM tb1 が基本となります。次に「売上のある社員だけ」というのは、「tb」と「tb1」の両方に「bang」が存在するとことなので
□WHERE * tb.bang=tb1.bang が条件となります。「1段階目」として、この条件に合うレコードを「tb」から選びます。
□SELECT * FROM tb WHERE tb.bang=tb1.bang これは、特定の「カラム」でなく、条件に合う「レコード」を抽出しているわけです。さらに「EXISTS」を使って「2段階目」としてこのレコードが存在しているものだけテーブル「tb1」から抽出します。結局次のようになります。

●操作手順

①次を実行する
SELECT *
FROM tb1
WHERE EXISTS
(SELECT * FROM tb WHERE tb.bang=tb1.bang);

●実行結果

mysql> SELECT *
-> FROM tb1
-> WHERE EXISTS
-> (SELECT * FROM tb WHERE tb.bang=tb1.bang);
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高橋 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡辺 | 23 |
+------+------+------+
4 rows in set (0.00 sec)

mysql>

(5-8)<今度はNOTEXISTS p225>

逆に「NOT EXISTS」は、サブクエリによって抽出されないレコードを対象にして処理を行います。前項の逆に「売上のない社員だけ」のレコードを、サブクエリを使って教示してみましょう。これは、前項の「EXISTS」を「NOT EXISTS」に変えるだけです。 NOT EXISTSを使って、テーブル「tb」に存在しないレコードの「bang」だけ抽出し、テーブル「tb1」で該当するレコードを表示しよう。

●実行される内容 p225

画像】

●操作手順

①次を実行する
SELECT *
FROM tb1
WHERE NOT EXISTS
(SELECT * FROM tb WHERE tb.bang=tb1.bang);

□【実行結果】

mysql> SELECT *
-> FROM tb1
-> WHERE NOT EXISTS
-> (SELECT * FROM tb WHERE tb.bang=tb1.bang);
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A105 | 西沢 | 35 |
+------+------+------+
1 row in set (0.00 sec)

mysql>

(5-9)<順位付けの妙技 その2>p226

RDBMSで順位を入力するのはけっこう面倒な処理である・・・と自己結合で学びました。 ところが、サブクエリを使うと、いろんな方法で順位付けが出来るのです。しかも自己結合でやるよりも、わかりやすくなります。ここではサブクエリを使った順位付けの方法をご紹介します。 ※売上情報テーブル「tb」の、売上の順位を付けます。今回は別のテーブルを用意し、順位をカラムに入力します。次の考え方で、順位を付けます。 a.連続番号機能を付けたテーブルに、「uria」の順に並べたレコードを挿入。 b.自動的に入力される連続番号が順位になる。 具体的には次の手順で処理します。
①テーブル「tb」と同じ構造のテーブル「tb_zyun」を作成
②テーブル「tb_zyun」に連続番号機能のあるカラム「zyun」を追加
③テーブル「tb」を「uria」の順に「SELECT」するサブクエリを実行 ④サブクエリの結果をテーブル「tb_zyun」に「INSERT」
テーブル「tb_zyun」のカラム「zyun」に順位を入力します。
さて、順位を入れるクエリは具体的にはどうなるでしょうか? 連続番号機能とサブクエリを使う方法を考えて見ましょう。
※テーブル「tb」は「bang」「uria」「tosi」からできています。これにカラム「zyun」を加えたテーブル「tb_zyun」を作成し、テーブル「tb」のレコードをコピーします。そしてカラム「zyun」に「uria」の順位を入力してみましょう。

●実行される内容 p227

画像】

●操作手順

①次を実行する
CREATE TABLE tb_zyun LIKE tb;
②次を実行する
ALTER TABLE tb_zyun ADD zyun INT AUTO_INCREMENT PRIMARY KEY;
③次を実行する
INSERT INTO tb_zyun
(bang,uria,tuki)
(SELECT
bang,uria,tuki
FROM tb
ORDER BY uria DESC);

※上記①~③は次のような処理を行なっています。
①テーブル「tb」のカラム構造だけをコピーして、テーブルtb_zyunを作成
②テーブル「tb_zyun」に、連続番号機能を持つカラム「zyun」を加える
③サブクリエを使い、テーブル「tb」をカラム「uria」の降順に並べ替え、
「bang」「uria」「tuki」を「tb_zyun」に挿入
※③で、カラム「zyun」には連続番号が自動的に入力され、これが順位になります。

□【実行結果】

①mysql> CREATE TABLE tb_zyun LIKE tb;
Query OK, 0 rows affected (0.29 sec)

②mysql> ALTER TABLE tb_zyun ADD zyun INT AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

③mysql> INSERT INTO tb_zyun
-> (bang,uria,tuki)
-> (SELECT
-> bang,uria,tuki
-> FROM tb
-> ORDER BY uria DESC);
Query OK, 10 rows affected (0.10 sec)
Records: 10 Duplicates: 0 Warnings: 0

④mysql> SELECT * FROM tb_zyun;
+------+------+------+------+
| bang | uria | tuki | zyun |
+------+------+------+------+
| A101 | 300 | 5 | 1 |
| A102 | 205 | 6 | 2 |
| A101 | 184 | 4 | 3 |
| A104 | 181 | 4 | 4 |
| A103 | 101 | 4 | 5 |
| A104 | 93 | 5 | 6 |
| A107 | 87 | 6 | 7 |
| A102 | 54 | 5 | 8 |
| A103 | 17 | 5 | 9 |
| A103 | 12 | 6 | 10 |
+------+------+------+------+
10 rows in set (0.00 sec)

mysql>

<まとめ>

●複数のテーブルを合わせて表示する方法 ●複数のテーブルを内部結合して表示する方法 ●内部結合と外部結合の違い ●サブクエリによる2段階の抽出

<チェック>

□UNIONを使って、複数のテーブルのデータを合わせてSELECTできる □キー指定して内部結合することで、複数のテーブルからSELECTできる □左外部結合・右外部結合により、複数のテーブルの必要なデータをSELECTできる □サブクエリの意味を理解している □サブクエリによって得た値を条件にして、さらに抽出ができる

<練習問題>

問題1
  テーブル「tb」で、「uria」の少ないものから順位を付け、1番から順に、「bang」、「uria」、順位を表示させて下さい。

問題2
  次のテーブル「tb1」および「tb」で、テーブル「tb1」に存在するレコードだけテーブル「tb」から抽出して下さい。

●実行される内容 p230

《解答1》


問題1
次を実行します
SELECT a.bang,a.uria,COUNT(*)
FROM tb AS a
JOIN tb AS b
WHERE a.uria>=b.uria
GROUP BY a.uria;

●実行結果

mysql> SELECT a.bang,a.uria,COUNT(*)
-> FROM tb AS a
-> JOIN tb AS b
-> WHERE a.uria>=b.uria
-> GROUP BY a.uria;
+------+------+----------+
| bang | uria | COUNT(*) |
+------+------+----------+
| A103 | 12 | 1 |
| A103 | 17 | 2 |
| A102 | 54 | 3 |
| A107 | 87 | 4 |
| A104 | 93 | 5 |
| A103 | 101 | 6 |
| A104 | 181 | 7 |
| A101 | 184 | 8 |
| A102 | 205 | 9 |
| A101 | 300 | 10 |
+------+------+----------+
10 rows in set (0.20 sec)

mysql>

《解答2》


問題2
次を実行します
SELECT *
FROM tb
WHERE EXISTS
(SELECT * FROM tb1 WHERE tb.bang=tb1.bang);

【実行結果】

mysql> SELECT *
-> FROM tb
-> WHERE EXISTS
-> (SELECT * FROM tb1 WHERE tb.bang=tb1.bang);
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
+------+------+------+
9 rows in set (0.05 sec)

mysql>
※なお、テーブル「tb1」に「A107」が存在しないため、「A107」は表示されません。