SQL ゼロから始めるデータベース操作 第7章 テーブルの足し算・引き算
今回は、一見しただけでは???なテーブルの足し算・引き算をやっていきます。
実際にやってみると、あーそういうことか!と相槌を打つことうけあいです。
CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2010/06/29
- メディア: 大型本
- 購入: 39人 クリック: 484回
- この商品を含むブログ (16件) を見る
テーブルの足し算・引き算とは?
まずこの言葉、ぱっと意味が思いつきません。
イメージにしてみましょう。
やっぱりわかりません。
本書では、
「集合演算」という名前で呼ばれているものです。
とあります。
集合かよ!
集合というイメージになると、1つのテーブルを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句は最後に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でがんばろう!(と、逃げる。)
お後がよろしいようで。