集合演算とは
本ページで学習するのは、「集合演算」という名前で呼ばれている操作である。「集合」というと数学の世界では「物の集まり」を表すが、データベースの世界では「レコードの集合」を表す。「レコードの集合」とは具体的に言えば、もちろんテーブルはそうであるし、ビューやクエリの実行結果もそうである。
これまでも、テーブルからレコードを選択したり、データを登録したりする方法を学習してきたが、集合演算とは、レコード同士を足したり引いたりする、いわばレコードの「四則演算」である。集合演算を行うことで、片方のテーブルだけにあるレコードを集めた結果や、共通するレコードを集めた結果、片方のテーブルだけにあるレコードを集めた結果などを得ることができる。そして、このような集合演算を行うための演算子を「集合演算子」と呼ぶ。
テーブルの足し算－UNION
最初に紹介する集合演算子は、レコードの足し算を行うUNION（和）である。
実際に使い方を見る前に、サンプルのテーブルを1つ用意する。次のような、今まで使ってきたchojin（超人）テーブルと同じレイアウトで、テーブル名だけが異なる「team」というテーブルを作る。
teamテーブルを作成する。
CREATE TABLE team (id CHAR(4) NOT NULL, name VARCHAR(100) NOT NULL, kana VARCHAR(100) NOT NULL, category VARCHAR(100) NOT NULL, attribute VARCHAR(100) NOT NULL, height INTEGER NOT NULL, weight INTEGER NOT NULL, power INTEGER NOT NULL, origin VARCHAR(100) NOT NULL, created DATE NOT NULL, PRIMARY KEY (id));
teamテーブルには、以下のレコードを登録する。
START TRANSACTION;
INSERT INTO team VALUES ('0001','キン肉マン','きんにくまん','正義超人','キン肉マンチーム','185','90','95','キン肉星',NOW());
INSERT INTO team VALUES ('0002','テリーマン','てりーまん','正義超人','キン肉マンチーム','190','95','95','アメリカ合衆国',NOW());
INSERT INTO team VALUES ('0003','ロビンマスク','ろびんますく','正義超人','キン肉マンチーム','217','155','96','イギリス',NOW());
INSERT INTO team VALUES ('0004','ウォーズマン','うぉーずまん','正義超人','キン肉マンチーム','210','150','100','ロシア',NOW());
INSERT INTO team VALUES ('0005','ラーメンマン','らーめんまん','正義超人','キン肉マンチーム','209','130','97','中華人民共和国',NOW());
INSERT INTO team VALUES ('0006','ブロッケンＪｒ','ぶろっけんじゅにあ','正義超人','ソルジャーチーム','195','90','90','西ドイツ',NOW());
INSERT INTO team VALUES ('0014','バッファローマン','ばっふぁろーまん','悪魔超人','ソルジャーチーム','250','220','1000','スペイン',NOW());
INSERT INTO team VALUES ('0018','ザ・ニンジャ','ざ・にんじゃ','悪魔超人','ソルジャーチーム','190','115','360','日本',NOW());
INSERT INTO team VALUES ('0019','アシュラマン','あしゅらまん','悪魔超人','ソルジャーチーム','203','200','1000','魔界',NOW());
INSERT INTO team VALUES ('0045','ジェロニモ','じぇろにも','正義超人','ソルジャーチーム','180','80','83','アメリカ合衆国',NOW());
INSERT INTO team VALUES ('0046','ミートくん','ミートくん','正義超人','ソルジャーチーム','100','25','50','キン肉星',NOW());
INSERT INTO team VALUES ('0047','キン肉アタル','きんにくあたる','正義超人','ソルジャーチーム','197','102','108','キン肉星',NOW());
COMMIT;
それでは準備ができたところで、さっそくこの2つのテーブルを「chojinテーブル＋teamテーブル」というように足し算をしてみる。
SELECT id, name FROM chojin UNION SELECT id, name FROM team;
結果
+------+--------------------------------+ | id | name | +------+--------------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | （中略） | 0014 | バッファローマン | （中略） | 0018 | ザ・ニンジャ | | 0019 | アシュラマン | （中略） | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------------+
結果は、2つのテーブルに含まれていたレコードが、すべて網羅されるという形になる。この演算のイメージは、集合論の「和集合」である。
超人ID「0001」～「0006」の6つのレコードはどちらのテーブルにも存在していたので、重複して結果に出てくるように思うかもしれないが、UNIONに限らず集合演算子は、通常は重複行が排除される。
集合演算の注意事項
この重複行を結果に出すことも可能だが、その前に、集合演算子を使うときの一般的な注意事項を学んでおく。これはUNIONに限らず、この後で学習するすべての演算子に当てはまる注意事項である。
注意事項①－演算対象となるレコードの列数は同じであること
例えば、次のように片方の列数が2列なのに、片方が3列という足し算を行うことはできない。
SELECT id, name FROM chojin UNION SELECT id, name, power FROM team;
結果
ERROR 1222 (21000): The used SELECT statements have a different number of columns
注意事項②－足し算の対象となるレコードの列のデータ型が一致していること
左から数えて同じ位置にある列は、同じデータ型である必要がある。例えば、次のSQL文は、列数は同じでも、2列目のデータ型が数値型と日付型で不一致のため、エラーになる。
SELECT id, power FROM chojin UNION SELECT id, created FROM team;
※MySQLでは、型が異なっていても、暗黙のうちに型変換を行う。
注意事項③－SELECT文はどんなものを指定しても良い。ただし、ORDER BY句は最後に1つだけ
UNIONで足せるSELECT文は、どんなものでも構わない。これまでに学んだWHERE、GROUP BY、HAVINGといった句も使うことができる。ただし、ORDER BY句だけは全体として1つ最後につけられるだけである。
SELECT id, name FROM chojin WHERE category = '正義超人' UNION SELECT id, name FROM team WHERE category = '正義超人' ORDER BY id;
結果
+------+--------------------------+ | id | name | +------+--------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0007 | ウルフマン | | 0043 | スペシャルマン | | 0044 | カナディアンマン | | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------+
重複行を残す集合演算－ALLオプション
さて、それではUNIONの結果から重複行を排除しない構文を紹介する。UNIONの後ろに「ALL」というキーワードを追加するだけである。このALLオプションは、UNION以外の集合演算子でも同様に使用できる。
SELECT id, name FROM chojin WHERE category = '正義超人' UNION ALL SELECT id, name FROM team WHERE category = '正義超人';
結果
+------+--------------------------+ | id | name | +------+--------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0007 | ウルフマン | | 0043 | スペシャルマン | | 0044 | カナディアンマン | | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------+
