集合としてのSQL

投稿日：2016年11月22日更新日：2020年5月12日

今まで何度かSQLが集合ということを扱ってきました。

今回はそれがよくわかる例を。

実務ではあまりないと思うのですが、集合をイメージしやすい例題として、２つのテーブルが全く同じケースを考えます。

下記のような２つのテーブルがあるとします。(tbl_a,tbl_b)

keycol | col_1 | col_2 | col_3
——–+——-+——-+——-
A | 2 | 3 | 4
B | 0 | 7 | 9
C | 5 | 1 | 6

この２つが全く同じかどうかはどう求めればよいのでしょうか。

実はUNIONを使えばOKのようです。

SELECT
    COUNT(*) AS row_cnt
FEOM
    ( SELECT * FROM tbl_a UNION SELECT * FROM tbl_b ) tmp;

SELECT

COUNT(*) AS row_cnt

FEOM

( SELECT * FROM tbl_a UNION SELECT * FROM tbl_b ) tmp;

上記のSQLで行数がtbl_a(or tbl_b)とまったく同じになっていればOKです。

UNIONは全く同一の行を削除するからです。

UNIONは集合でいうと和集合を表し、上記の例だと２つの集合が完全に一致します。もし１レコードでも違うものがあれば数は４になります。

ちなみにSQLでは差集合も存在しまして、例えば下記のSQLだとなんのレコードもでてきませんが、もし違いがあればそのレコードが出てきます。

 SELECT
   *
 FROM 
    ( SELECT * FROM tbl_a 
      EXCEPT
      SELECT * FROM tbl_b ) tmp;

SELECT

FROM

( SELECT * FROM tbl_a

EXCEPT

SELECT * FROM tbl_b ) tmp;

これを使って２つのテーブルが等しいか等しくないかを下記のSQLで判断できます。

ちなみにUNION(和集合)、INTERSECT(共通集合)、EXCEPT(差集合)は下記リンクのページのベン図で見るのが一番わかりやすいかと思います。

8. クエリの結合

集合論を使って問題を解いてみましょう。

例えば下記のようなskillsテーブルがあるとします。

skill
——–
Oracle
UNIX
Java

そして、下記テーブル(empskills)をみて、skillをすべて所有しているメンバーを抽出するにはどうしたらいいか考えてみましょう。

この問題はこのページに「すべての商品を持っている店舗はどれか」という問題と近いですね。というか趣旨自体は全く同じですね。

例えばある個人を特定し、下記のようなSQLを投げた場合、その人物が持っているskillが帰ってきます。これとskillsテーブルを比べて差分がなければすべてのスキルをマスターしているということになります。

SELECT
  skill
FROM
  empskills es1
WHERE
  es1.emp ='相田'

SELECT

skill

FROM

empskills es1

WHERE

es1.emp ='相田'

例えば相田さんのスキルをskillsテーブルとの差分を取ってみましょう。

SELECT
  *
FROM
  skills
EXCEPT
SELECT
  es1.skill
FROM
  empskills es1
WHERE
  es1.emp ='相田'

SELECT

FROM

skills

EXCEPT

SELECT

es1.skill

FROM

empskills es1

WHERE

es1.emp ='相田'

この場合結果が返ってきません。つまりレコードが存在していないわけなんですね。この状態が正しい状態です。あとはempを変数化し、NOT EXISTSを使えばいいのです。

答えのSQLは下記のようになります。

SELECT
  -- DISTINTがないと該当者のレコードがすべて出力される
  DISTINCT(es2.emp)
FROM 
  empskills es2
WHERE 
  NOT EXISTS (
      SELECT *  FROM  skills 
    EXCEPT 
      SELECT es1.skill FROM  empskills es1 WHERE es1.emp = es2.emp
)

SELECT

-- DISTINTがないと該当者のレコードがすべて出力される

DISTINCT(es2.emp)

FROM

empskills es2

WHERE

NOT EXISTS (

SELECT * FROM skills

EXCEPT

SELECT es1.skill FROM empskills es1 WHERE es1.emp = es2.emp

)

イメージ的には以前のリンクの問題よりはわかりやすいかなあと思います。