SQLの集合演算整理

sql

はじめに

SQL は集合的思考が必要ということだが、和集合や差集合など求める集合演算の仕方は複数存在するようなので、一度整理しようと思う。

  1. 集合演算子形式
  2. exist を使った述語論理形式
  3. (外部・内部)結合形式
    ３パターンで考えてみた。

データの準備

まずデータベースにデータを準備する。 今回は、英語クラスと数学クラスを受講している生徒の情報を記録したテーブルを用意して考える。

-- 英語クラステーブル作成
CREATE TABLE `schemaName`.`english` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

-- データ挿入
insert into english value(1, '一浪'), (2, '二浪'), (3, '三浪');

-- 数学クラステーブル作成
CREATE TABLE `schemaName`.`math` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`id`));

-- データ挿入
insert into math value(1, '三浪'), (2, '四浪'), (3, '五浪');

和集合

英語あるいは数学の少なくとも一方を受けている生徒をみつける

  1. 集合演算子(MySQL は不可能)
  select name from english union select name from math;
  1. 述語論理
不可能...
  1. 外部結合
不可能...

差集合

英語を受講しているが、数学は受講していない生徒をみつける

  1. 集合演算子(MySQL は不可能)
select * from english except math;
  1. 述語論理
select * from english e where not exists(select * from math m where e.name = m.name);
  1. 結合
select * from english e left join math m on e.name = m.name where m.name is null;

積集合

英語と数学両方を受講している生徒をみつける

  1. 集合演算子(MySQL は不可能)
select * from english intersect math
  1. 述語論理
select * from english e where exists(select * from math m where e.name = m.name);
  1. 結合
select e.name from english e inner join math m on e.name = m.name;

まとめ

MySQL は、和集合除いて集合演算子は使えないみたいですね。
その代わりに結合形式や述語論理形式をつかった表現力が必要になったのだろうと思う。

なぜ述語論理形式で集合演算が可能なのかなと考えてみると、existsが 引数に集合をとれることと、さらに相関サブクエリによって２つの集合の要素(行)同士を比較することができるからなのであろうということはちょっとした気付きになりました。