skillup

技術ブログ

Database

集合としてのSQL

投稿日:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

http://www.techscore.com/tech/sql/08_01.html/#sql82

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

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

skill
——–
Oracle
UNIX
Java

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

emp | skill
——–+——–
相田 | Oracle
相田 | UNIX
相田 | Java
相田 | C#
神崎 | Oracle
神崎 | UNIX
神崎 | Java
平井 | UNIX
平井 | Oracle
平井 | PHP
平井 | Perl
平井 | C++
若田部 | Perl
渡来 | Oracle

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

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

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

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

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

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

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

JPQLでの算術関数

複雑なJPQLを書いていると、通常のレコードの取り出しだけではなく、合計(SUM)や算出(COUNT)などのいわゆる算術関数を使うことが一般的です。 JPQLでもこれらを通常通り扱うことができます。 …

no image

論理設計のアンチパターン

今回からは論理設計のアンチパターンについて。 やってはいけない設計のパターンですね。これはまわりがやっていると気づかずにやっている可能性があるのでしっかりメモしておきたいです。 Contents1 非 …

no image

MySQLのパフォーマンスチェックなどについて

常日頃MySQLをつかっているのですがパフォーマンスのチェックなどをあまりしていなかったため、これをチョクチョクしていこうかなあと思っております。 簡単に使えるツール(ただし5.1.4から)としては標 …

no image

GlassFishでDB接続

一般的にWEB系の言語で、DBを使う場合、WEBサーバーとDBサーバーは単独に動くことが一般的です。 JavaEEではアプリケーションサーバーとしてGlassFishを使いますが、先日、GlassFi …

no image

アンチパターン 参照渡しと値渡し+キー情報の設定+同一値を複数テーブルに配置+正規化が不十分+集計表+不適切なステータス値

本日は自分がデータベースの設計をしていて気を付かないといけないなーと思った点などを。 注意点としては設計のミスは実装で取り返しにくいことが多いので極力気を付けましょう。あといろいろなテーブルのパターン …