SQL ゼロから始めるデータベース操作 第7章 テーブルの足し算・引き算

今回は、一見しただけでは？？？なテーブルの足し算・引き算をやっていきます。
実際にやってみると、あーそういうことか！と相槌を打つことうけあいです。

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

テーブルの足し算・引き算とは？

まずこの言葉、ぱっと意味が思いつきません。
イメージにしてみましょう。

やっぱりわかりません。

本書では、

「集合演算」という名前で呼ばれているものです。

とあります。

集合かよ！

集合というイメージになると、1つのテーブルを１つの集合と考えれば、こうですね。
ちなみにこういう図をベン図と言うらしいです。(高校で勉強しているはずだけど忘れた…)

(ベン図を書く良いツールってあるんですかねぇ)

つまり、先ほどのテーブルで言えば、2つのテーブルのデータを1つのテーブルにまとめる、というイメージです。

ここで問題になるのは、両方のテーブルにいるBellです。
和集合で言えばBellは重複するので1人だけ、でも足し算で考えるとそのまま足し算なら2人になるよねとなりますよね。
正解としては、どっちもあり得るというのがSQLの処理になります。

テーブルとテーブルの足し算 UNION

この和集合、テーブルとテーブルの足し算を実現する機能が、UNIONという句です。

文法は、以下のようになっています。

SELECT *
FROM TABLE1
UNION
SELECT *
FROM TABLE2

今までのSQL文と比べると簡単です。2つのSELECT文をUNIONで接続するだけです。

イメージと同じデータで実際にやってみます。

mysql> SELECT * FROM User1;
+----+------+-----------+------+
| id | Name | Address   | Age  |
+----+------+-----------+------+
|  1 | Bell | Seattle   |   24 |
|  2 | Jack | Cupertino |   27 |
|  3 | Mor  | San Diego |   19 |
|  4 | Mike | New York  |   38 |
+----+------+-----------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM User2;
+----+------+---------+------+
| id | Name | Address | Age  |
+----+------+---------+------+
|  1 | Bell | Seattle |   24 |
|  2 | Jiro | Osaka   |   23 |
|  3 | Hana | Fukuoka |   31 |
|  4 | Maru | Sapporo |   45 |
+----+------+---------+------+
4 rows in set (0.00 sec)

このUser1,User2をUNIONで繋いでみると、

mysql> SELECT * FROM User1
    -> UNION
    -> SELECT * FROM User2;
+----+------+-----------+------+
| id | Name | Address   | Age  |
+----+------+-----------+------+
|  1 | Bell | Seattle   |   24 |
|  2 | Jack | Cupertino |   27 |
|  3 | Mor  | San Diego |   19 |
|  4 | Mike | New York  |   38 |
|  2 | Jiro | Osaka     |   23 |
|  3 | Hana | Fukuoka   |   31 |
|  4 | Maru | Sapporo   |   45 |
+----+------+-----------+------+
7 rows in set (0.00 sec)

Bellが1人しかいないので、コレは和集合という形になりますね。
内部的には、重複するものは追加しないという感じの処理を行っています。

もし、重複を許可するのであれば、ALLオプションをつけることで、
すべてのデータを足したテーブルの足し算の結果が得られます。

mysql> SELECT * FROM User1
    -> UNION ALL
    -> SELECT * FROM User2;
+----+------+-----------+------+
| id | Name | Address   | Age  |
+----+------+-----------+------+
|  1 | Bell | Seattle   |   24 |
|  2 | Jack | Cupertino |   27 |
|  3 | Mor  | San Diego |   19 |
|  4 | Mike | New York  |   38 |
|  1 | Bell | Seattle   |   24 |
|  2 | Jiro | Osaka     |   23 |
|  3 | Hana | Fukuoka   |   31 |
|  4 | Maru | Sapporo   |   45 |
+----+------+-----------+------+
8 rows in set (0.00 sec)

今度はBellが2人いますね。

集合演算を使う時の注意点

本書では、注意点として以下の2つが紹介されています。

• 集合のデータ元となるすべてのSELECT文の列数は同じである必要があります。

例えば、User1は全部、User2は名前だけ、ということはできません。

mysql> SELECT * FROM User1
    -> UNION
    -> SELECT NAME FROM User2;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

• 対象となるレコードの列のデータ型が一致していること
  

例えば、User1はName列(VARCHAR型), User2はAge(INT型)を選択した場合、どうなるのでしょうか？
実際にやってみると、

mysql> SELECT NAME FROM User1
    -> UNION
    -> SELECT Age FROM User2;
+------+
| NAME |
+------+
| Bell |
| Jack |
| Mor  |
| Mike |
| 24   |
| 23   |
| 31   |
| 45   |
+------+
8 rows in set (0.00 sec)

あれ？できますね。
これは、MySQLが暗黙の型変換をやってくれるので、Age列がVARCHAR型になっているのです。
ただし、これはMySQLがやってくれているだけで、他のDBMSでできる保証はありません。
なので、こういったシチュエーションの場合、型変換を行う関数CASTを使うことが推奨されています。

• ORDER BY句は最後に１つだけ
  

コレはイメージが湧くと思いますが、ORDER BY句は最後に１つだけ、と決められています。
もし、中間にORDER BY句を入れた場合、

mysql> SELECT * FROM User1
    -> ORDER BY Age
    -> UNION
    -> SELECT * FROM User2;
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

以上のように怒られてしまいます。

その他の集合演算

和集合を行うUNIONだけではなく、共通集合を行うINTERSECT, 差集合を行うEXCEPTという関数が標準SQL規約に定義されており、様々なDBMSで使うことができます。

MySQL以外。

MySQL以外・・・。

余り使う機会がない関数なのか、MySQLは対応していません。

なので、デモもできないので解説できません。。。

PostgreSQLに乗り換えようかな。
MySQLもろくにわかっていないので、しばらくMySQLでがんばろう！(と、逃げる。)
お後がよろしいようで。