Blank?=False

「呉下の阿蒙にあらず」をモットーにしたITエンジニアの日々

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

f:id:stonebeach-dakar:20160424222031p:plain

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

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

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

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

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

f:id:stonebeach-dakar:20160511215438p:plain やっぱりわかりません。

本書では、

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

とあります。

集合かよ!


集合というイメージになると、1つのテーブルを1つの集合と考えれば、こうですね。
ちなみにこういう図をベン図と言うらしいです。(高校で勉強しているはずだけど忘れた…)
f:id:stonebeach-dakar:20160511213640p:plain
(ベン図を書く良いツールってあるんですかねぇ)

つまり、先ほどのテーブルで言えば、2つのテーブルのデータを1つのテーブルにまとめる、というイメージです。
f:id:stonebeach-dakar:20160511214200p:plain

ここで問題になるのは、両方のテーブルにいる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句は最後に1つだけ

コレはイメージが湧くと思いますが、ORDER BY句は最後に1つだけ、と決められています。
もし、中間に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でがんばろう!(と、逃げる。)
お後がよろしいようで。