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

SQL基礎 case&groupbyの活用について

主に集計タイプの計算で大活躍するgroupbyについて。 主な用途は集計とカット。特にcase式と連動した集計はかなり使える 例 nameとageで構成されたテーブルがあるとして、年代ごとの人数を出し …

no image

アンチパターン バインド変数の未使用+直積組み合わせ+データ量爆発+インデックス関連

本日はSQLコーディングに関して。 ここら辺は実際にプログラムを書く際に重要になってくるネタ。 Contents1 バインド変数1.1 デメリット1.2 対策2 直積により組み合わせが爆発する2.1 …

no image

SQLにおけるナンバリング

本日はナンバリングに関して。 MySQLを使っていますと各テーブルにはid int not null auto_increment primary keyなどと打って主キーを打つことがほぼ習慣になって …

no image

HAVING句がらみの計算式

「達人に学ぶSQL徹底指南書」読んでますが、かなり難しいものがでてきましたね。 テーブルは前回のものと同じ以下のものを使います。 name | income ————+——– サンプソン | 4000 …

no image

リレーションを含んだテーブルでの副問い合わせ

本日はSQLネタです。 下記のようなテーブル構成があったときとします。 注文ヘッダと注文詳細は(1:N)とします。 ここで、product_id=5を含んだ注文ヘッダーレコードを取り出したいとします。 …