あるテーブルのあるフィールドの値が別のテーブルのあるフィールドに一致するものが ないものだけ抜き出す方法。

例えば、毎日の売り上げを記録した”days”というテーブルと、
祝日をの情報を持ったテーブル”holiday”があるとします。

ここで、祝日以外の日の売り上げ平均を見たい場合、
以下のようにすることで抽出できます。

SELECT AVG(sales) FROM days as d LEFT OUTER JOIN holiday as h ON d.year=h.year AND d.month=h.month AND d.day=h.day WHERE h.id IS NULL  group by d.year;

テーブルの結合2(等価結合)

単純結合だと無駄な行がたくさんできてしまいます。

そこで登場するのが等価結合で、二つの表について一致している行のみ結合させます。

member表に追加で各メンバーの出身地の人口(population)を表示させるにさせる
例を以下に示します。

mysql> select member.name, member.age, member.address, address.population from member, address where member.address=address.address;
+----------+------+---------+------------+
| name     | age  | address | population |
+----------+------+---------+------------+
| tanaka   |   30 | 東京  | 13216221   |
| suzuki   |   32 | 東京  | 13216221   |
| nakamura |   33 | 群馬  | 1992432    |
| miura    |   31 | 埼玉  | 7208122    |
| kimura   |   27 | 茨城  | 2946194    |
| takai    |   25 | 千葉  | 6196164    |
| tadano   |   26 | 千葉  | 6196164    |
| murakami |   35 | 茨城  | 2946194    |
| kikuchi  |   32 | 茨城  | 2946194    |
| suzuki   |   31 | 茨城  | 2946194    |
| saeki    |   29 | 埼玉  | 7208122    |
| ooyama   |   29 | 埼玉  | 7208122    |
| kamata   |   22 | 埼玉  | 7208122    |
+----------+------+---------+------------+
13 rows in set (0.00 sec)

テーブルの結合1(単純結合)

結合には以下のように種類がいくつかあります。

  • 単純結合
  • 等価結合
  • 非等価結合
  • 外部結合
  • 再起結合

今回はこの中で、単純結合のご紹介をします。

まず、以下の二つのテーブルがあるとします。
これら二つを単純結合すると「 x 」の
行数のテーブルが出来上がります。

テーブル1

mysql> select * from member;
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  1 | tanaka   |   30 |     57 | 東京  |
|  2 | suzuki   |   32 |     77 | 東京  |
|  3 | nakamura |   33 |     52 | 群馬  |
|  4 | miura    |   31 |     67 | 埼玉  |
|  5 | kimura   |   27 |     62 | 茨城  |
|  6 | takai    |   25 |     63 | 千葉  |
|  7 | tadano   |   26 |     43 | 千葉  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 10 | suzuki   |   31 |     51 | 茨城  |
| 11 | saeki    |   29 |     49 | 埼玉  |
| 12 | ooyama   |   29 |     48 | 埼玉  |
| 13 | kamata   |   22 |     47 | 埼玉  |
| 14 | mizumoto |   35 |     55 | NULL    |
+----+----------+------+--------+---------+
14 rows in set (0.00 sec)

テーブル2

mysql> select * from address;
+----+---------+------------+
| id | address | population |
+----+---------+------------+
|  1 | 東京  | 13216221   |
|  2 | 群馬  | 1992432    |
|  3 | 茨城  | 2946194    |
|  4 | 埼玉  | 7208122    |
|  5 | 千葉  | 6196164    |
+----+---------+------------+
5 rows in set (0.00 sec)

テーブル1とテーブル2を単純結合してできるテーブル

mysql> select * from member, address;
+----+----------+------+--------+---------+----+---------+------------+
| id | name     | age  | weight | address | id | address | population |
+----+----------+------+--------+---------+----+---------+------------+
|  1 | tanaka   |   30 |     57 | 東京  |  1 | 東京  | 13216221   |
|  1 | tanaka   |   30 |     57 | 東京  |  2 | 群馬  | 1992432    |
|  1 | tanaka   |   30 |     57 | 東京  |  3 | 茨城  | 2946194    |
|  1 | tanaka   |   30 |     57 | 東京  |  4 | 埼玉  | 7208122    |
|  1 | tanaka   |   30 |     57 | 東京  |  5 | 千葉  | 6196164    |
|  2 | suzuki   |   32 |     77 | 東京  |  1 | 東京  | 13216221   |
|  2 | suzuki   |   32 |     77 | 東京  |  2 | 群馬  | 1992432    |
|  2 | suzuki   |   32 |     77 | 東京  |  3 | 茨城  | 2946194    |
|  2 | suzuki   |   32 |     77 | 東京  |  4 | 埼玉  | 7208122    |
|  2 | suzuki   |   32 |     77 | 東京  |  5 | 千葉  | 6196164    |
|  3 | nakamura |   33 |     52 | 群馬  |  1 | 東京  | 13216221   |
|  3 | nakamura |   33 |     52 | 群馬  |  2 | 群馬  | 1992432    |
|  3 | nakamura |   33 |     52 | 群馬  |  3 | 茨城  | 2946194    |
|  3 | nakamura |   33 |     52 | 群馬  |  4 | 埼玉  | 7208122    |
|  3 | nakamura |   33 |     52 | 群馬  |  5 | 千葉  | 6196164    |
|  4 | miura    |   31 |     67 | 埼玉  |  1 | 東京  | 13216221   |
|  4 | miura    |   31 |     67 | 埼玉  |  2 | 群馬  | 1992432    |
|  4 | miura    |   31 |     67 | 埼玉  |  3 | 茨城  | 2946194    |
|  4 | miura    |   31 |     67 | 埼玉  |  4 | 埼玉  | 7208122    |
|  4 | miura    |   31 |     67 | 埼玉  |  5 | 千葉  | 6196164    |
|  5 | kimura   |   27 |     62 | 茨城  |  1 | 東京  | 13216221   |
|  5 | kimura   |   27 |     62 | 茨城  |  2 | 群馬  | 1992432    |
|  5 | kimura   |   27 |     62 | 茨城  |  3 | 茨城  | 2946194    |
|  5 | kimura   |   27 |     62 | 茨城  |  4 | 埼玉  | 7208122    |
|  5 | kimura   |   27 |     62 | 茨城  |  5 | 千葉  | 6196164    |
|  6 | takai    |   25 |     63 | 千葉  |  1 | 東京  | 13216221   |
|  6 | takai    |   25 |     63 | 千葉  |  2 | 群馬  | 1992432    |
|  6 | takai    |   25 |     63 | 千葉  |  3 | 茨城  | 2946194    |
|  6 | takai    |   25 |     63 | 千葉  |  4 | 埼玉  | 7208122    |
|  6 | takai    |   25 |     63 | 千葉  |  5 | 千葉  | 6196164    |
|  7 | tadano   |   26 |     43 | 千葉  |  1 | 東京  | 13216221   |
|  7 | tadano   |   26 |     43 | 千葉  |  2 | 群馬  | 1992432    |
|  7 | tadano   |   26 |     43 | 千葉  |  3 | 茨城  | 2946194    |
|  7 | tadano   |   26 |     43 | 千葉  |  4 | 埼玉  | 7208122    |
|  7 | tadano   |   26 |     43 | 千葉  |  5 | 千葉  | 6196164    |
|  8 | murakami |   35 |     42 | 茨城  |  1 | 東京  | 13216221   |
|  8 | murakami |   35 |     42 | 茨城  |  2 | 群馬  | 1992432    |
|  8 | murakami |   35 |     42 | 茨城  |  3 | 茨城  | 2946194    |
|  8 | murakami |   35 |     42 | 茨城  |  4 | 埼玉  | 7208122    |
|  8 | murakami |   35 |     42 | 茨城  |  5 | 千葉  | 6196164    |
|  9 | kikuchi  |   32 |     41 | 茨城  |  1 | 東京  | 13216221   |
|  9 | kikuchi  |   32 |     41 | 茨城  |  2 | 群馬  | 1992432    |
|  9 | kikuchi  |   32 |     41 | 茨城  |  3 | 茨城  | 2946194    |
|  9 | kikuchi  |   32 |     41 | 茨城  |  4 | 埼玉  | 7208122    |
|  9 | kikuchi  |   32 |     41 | 茨城  |  5 | 千葉  | 6196164    |
| 10 | suzuki   |   31 |     51 | 茨城  |  1 | 東京  | 13216221   |
| 10 | suzuki   |   31 |     51 | 茨城  |  2 | 群馬  | 1992432    |
| 10 | suzuki   |   31 |     51 | 茨城  |  3 | 茨城  | 2946194    |
| 10 | suzuki   |   31 |     51 | 茨城  |  4 | 埼玉  | 7208122    |
| 10 | suzuki   |   31 |     51 | 茨城  |  5 | 千葉  | 6196164    |
| 11 | saeki    |   29 |     49 | 埼玉  |  1 | 東京  | 13216221   |
| 11 | saeki    |   29 |     49 | 埼玉  |  2 | 群馬  | 1992432    |
| 11 | saeki    |   29 |     49 | 埼玉  |  3 | 茨城  | 2946194    |
| 11 | saeki    |   29 |     49 | 埼玉  |  4 | 埼玉  | 7208122    |
| 11 | saeki    |   29 |     49 | 埼玉  |  5 | 千葉  | 6196164    |
| 12 | ooyama   |   29 |     48 | 埼玉  |  1 | 東京  | 13216221   |
| 12 | ooyama   |   29 |     48 | 埼玉  |  2 | 群馬  | 1992432    |
| 12 | ooyama   |   29 |     48 | 埼玉  |  3 | 茨城  | 2946194    |
| 12 | ooyama   |   29 |     48 | 埼玉  |  4 | 埼玉  | 7208122    |
| 12 | ooyama   |   29 |     48 | 埼玉  |  5 | 千葉  | 6196164    |
| 13 | kamata   |   22 |     47 | 埼玉  |  1 | 東京  | 13216221   |
| 13 | kamata   |   22 |     47 | 埼玉  |  2 | 群馬  | 1992432    |
| 13 | kamata   |   22 |     47 | 埼玉  |  3 | 茨城  | 2946194    |
| 13 | kamata   |   22 |     47 | 埼玉  |  4 | 埼玉  | 7208122    |
| 13 | kamata   |   22 |     47 | 埼玉  |  5 | 千葉  | 6196164    |
| 14 | mizumoto |   35 |     55 | NULL    |  1 | 東京  | 13216221   |
| 14 | mizumoto |   35 |     55 | NULL    |  2 | 群馬  | 1992432    |
| 14 | mizumoto |   35 |     55 | NULL    |  3 | 茨城  | 2946194    |
| 14 | mizumoto |   35 |     55 | NULL    |  4 | 埼玉  | 7208122    |
| 14 | mizumoto |   35 |     55 | NULL    |  5 | 千葉  | 6196164    |
+----+----------+------+--------+---------+----+---------+------------+
70 rows in set (0.00 sec)

副問い合わせ(3)

これまでは、where句において副問い合わせを使用しましたが、FROM句でも使えます。

複数条件での検索」では以下のような

mysql> SELECT * FROM member WHERE age<32 AND address="東京";

これを、副問い合わせを使って以下のようにもできます。

mysql> select * from (select * from member where address='東京') tokyo where age < 32;
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
|  1 | tanaka |   30 |     57 | 東京  |
+----+--------+------+--------+---------+

副問い合わせ(2)

副問い合わせの結果が1列の場合の例は出しましたが、結果が複数列の場合の
例を示します。

以下の例では、saekiさんと年齢と出身地が同じメンバーを抽出します。

mysql> select * from member where (age, address) = (select age, address from member where name='saeki');
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
| 11 | saeki  |   29 |     49 | 埼玉  |
| 12 | ooyama |   29 |     48 | 埼玉  |
+----+--------+------+--------+---------+
2 rows in set (0.00 sec)

副問い合わせ(1)

例えば、miuraさんと同じ都道府県に住んでいるメンバーを表示したい場合、
mysqlへのアクセスを2回行う必要があります。

1回目は以下のような命令で、miuraさんのaddressを確認します。

mysql> select * from member where name='miura';

2回目は、1回目のクエリで得られた結果を元に以下のようなクエリを発行します。

mysql> select * from member where address='埼玉';
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
|  4 | miura  |   31 |     67 | 埼玉  |
| 11 | saeki  |   29 |     49 | 埼玉  |
| 12 | ooyama |   29 |     48 | 埼玉  |
| 13 | kamata |   22 |     47 | 埼玉  |
+----+--------+------+--------+---------+
4 rows in set (0.00 sec)

これを一回のクエリにまとめるための手法が副問い合わせです。

副問い合わせを使えば、以下のように上記の2回のクエリを1回にまとめられます。

mysql> select * from member where address = ( select address from member where name='miura');
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
|  4 | miura  |   31 |     67 | 埼玉  |
| 11 | saeki  |   29 |     49 | 埼玉  |
| 12 | ooyama |   29 |     48 | 埼玉  |
| 13 | kamata |   22 |     47 | 埼玉  |
+----+--------+------+--------+---------+
4 rows in set (0.00 sec)

これは、ベースとなるselect文のwhere句のaddressの条件として、「select address from member where name=’miura’」の
結果を使っています。このselect文が副問い合わせになります。
結果として、先ほどのクエリを2回出したときの結果と同じになります。

副問い合わせが複数行の場合

上の例では副問い合わせの結果が1行だからできますが、副問い合わせのselectの結果が複数行になる可能性がある場合はJOIN句を使用します。

mysql> select * from member where address in ( select address from member where name in ('tanaka', 'kimura'));
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  1 | tanaka   |   30 |     57 | 東京  |
|  2 | suzuki   |   32 |     77 | 東京  |
|  5 | kimura   |   27 |     62 | 茨城  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 10 | suzuki   |   31 |     51 | 茨城  |
+----+----------+------+--------+---------+
6 rows in set (0.00 sec)