skillup

技術ブログ

Database

EXISTSについて

投稿日:

今回はEXISTSについてです。

実務では伝票と明細との検索関連の処理で結構出てきます。

なお、達人に学ぶ~では論理学について少しふれており、この領域を本気で理解する場合は論理学を勉強する必要があります。

下記のようなテーブル(meetings)があり、欠席者を求めるとします。

meeting | person
————————————-+————————————
第1回 | 伊藤
第1回 | 水島
第1回 | 坂東
第2回 | 伊藤
第2回 | 宮田
第3回 | 坂東
第3回 | 水島
第3回 | 宮田

求めたいレコードは下記のようなものです。

meeting | person
————————————-+————————————
第1回 | 宮田
第2回 | 坂東
第2回 | 水島
第3回 | 伊藤

ここに出ているメンバーで全員と考えていいので、まずはメンバー全員のリストを求めればいいのでは・・・と思ったのですが、それだけやっても求められないと気づきました。

基本的な考え方ですが、皆勤だった場合のテーブルを考えます。

皆勤テーブルは下記のように求められます。

自己結合ですね。この中で回数とpersonでの重複を除き、全員出そうとおもうと、上記のようにDISTINCTをとります。実際にSELECTで全列を出力してみて、重複を取り除いて、全員が出席している場合を抽出しようとすると必然的にこのようになります。

EXISTSうんぬんよりはここがちょっとむずいかもです。

考え方としては皆勤テーブルをベースに考え、皆勤テーブルにあってmembersの中にないレコードを探せればOKです。そのSQLですが、下記のようになります。

ただしもっと簡単な方法がありまして、前回やった集合の考えを使えば差分を取れば一発です。

否定と肯定の言い換え

EXISTSを使ってSQLで条件を検索する場合、肯定の表現を否定の表現を入れ替えることが多いです。

例えば下記のようなテーブル(testscores)があると仮定して、全ての点数が50点以上の生徒を求めてみましょう。

student_id | subject | score
————+———+——-
100 | 算数 | 100
100 | 国語 | 80
100 | 理科 | 80
200 | 算数 | 80
200 | 国語 | 95
300 | 算数 | 40
300 | 国語 | 90
300 | 社会 | 55
400 | 算数 | 80

この場合の条件の作り方ですが、全てが50点以上というよりも50点未満の点数を持っていないという条件分岐のほうが簡単です。

50点以上の点数を保持しているだと、一教科でも50点以上ならカウントされてしまうためEXISTSでは取得できません。

この場合、NOT EXISTSを使って簡単に作れます。

結果として下記のようなSQLを作ればOKです。

今度は若干複雑になりまして、教科が算数ならば80点以上であり、国語ならば50点以上の生徒を求めてみます。

まず下記のように算数と国語で基準点に達した生徒にフラグを立てるようにし、これで求めようとおもいました。

これでいけるかと思ったんですがWHERE句の中でmathやjapaneseが使えないことが発覚。

この場合は下記のように書くようです。

これもだめでした。これだと算数&80で国語&50点というのが1行で成り立っていないとダメなようです。
下記のように直します
※こういった複雑なSQLの場合はEXISTS以下だけでチェックしてみたりするのが良いです。

これで行けるかと思いましたが、やはりこれだと算数、国語どっちかの条件に合致した場合出てきてしまうので否定で考えます。

student_id | subject | score
————+———+——-
100 | 算数 | 100
100 | 国語 | 80
100 | 理科 | 80
200 | 算数 | 80
200 | 国語 | 95
400 | 算数 | 80
こうなりました。

さらに条件を絞り、subjectが国語と算数のみでstudent_idでグルーピングし、行数が2つ(国語と算数両方必要なため)あるものを絞り込みます。

student_id | count
————+——-
100 | 2
200 | 2

これでOKです。長かった・・・

-Database
-

執筆者:


comment

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

関連記事

no image

HAVING句の活用 発展編

今回からはまたまたHAVING句です。 下記のようなテーブル(teams)があり、全員が待機状態のチーム(全員がそろっている)を求めます。 member | team_id | status &#82 …

no image

集合としてのSQL

今まで何度かSQLが集合ということを扱ってきました。 今回はそれがよくわかる例を。 実務ではあまりないと思うのですが、集合をイメージしやすい例題として、2つのテーブルが全く同じケースを考えます。 下記 …

no image

SQL基礎 結合に関して

SQL実践入門を読んで勉強しておりますが、本日は結合に関して。 Contents1 クロス結合2 内部結合3 外部結合4 結合のアルゴリズムとパフォーマンス4.1 NestedLoops4.2 Has …

no image

MySQL.sockファイルに関して

朝出社してテストサーバーを見るといきなりサーバーが動いていないという事態が発生。 MySQLを起動しようとすると

なるメッセージがでて …

no image

MySQLの大文字、小文字問題について

MySQLでテーブルを作る際にカラムに重複を許さない制約(unique)を振ることがあると思います。 数字の場合は問題ないのですが、文字列の場合、なんと英語の大文字と小文字が区別されません。 つまりa …