EXISTSについて

今回はEXISTSについてです。

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

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

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

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

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

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

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

SELECT
  DISTINCT m1.meeting, m2.person
FROM 
  meetings m1 , meetings m2

SELECT

DISTINCT m1.meeting, m2.person

FROM

meetings m1 , meetings m2

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

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

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

 SELECT
     kaikin.*
  FROM
    ( SELECT
        DISTINCT m1.meeting, m2.person
      FROM 
       meetings m1 , meetings m2
    ) AS kaikin
   WHERE  
     NOT EXISTS( 
       SELECT
         *
       FROM 
         meetings m3 
       WHERE
         kaikin.meeting =  m3.meeting AND
         kaikin.person = m3.person
   )

SELECT

kaikin.*

FROM

( SELECT

DISTINCT m1.meeting, m2.person

FROM

meetings m1 , meetings m2

) AS kaikin

WHERE

NOT EXISTS(

SELECT

FROM

meetings m3

WHERE

kaikin.meeting = m3.meeting AND

kaikin.person = m3.person

)

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

 SELECT
     DISTINCT m1.meeting, m2.person
 FROM 
    meetings m1 , meetings m2
 EXCEPT    
 SELECT
    m3.*
 FROM 
   meetings m3

SELECT

DISTINCT m1.meeting, m2.person

FROM

meetings m1 , meetings m2

EXCEPT

SELECT

m3.*

FROM

meetings m3

否定と肯定の言い換え

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です。

SELECT
  DISTINCT ts2.student_id
FROM
  testscores ts2
WHERE
  NOT EXISTS (
    SELECT
      *
    FROM
      testscores ts1
    WHERE
      ts1.score < 50 AND ts2.student_id = ts1.student_id
)

SELECT

DISTINCT ts2.student_id

FROM

testscores ts2

WHERE

NOT EXISTS (

SELECT

FROM

testscores ts1

WHERE

ts1.score < 50 AND ts2.student_id = ts1.student_id

)

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

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

 SELECT
   ts2.*
 FROM
   testscores ts2
 WHERE
   EXISTS (
     SELECT
      ts1.*,
      CASE WHEN subject = '算数' AND score >= 80 THEN 'A'  ELSE NULL END AS math,
      CASE WHEN subject = '国語' AND score >= 50 THEN 'A'  ELSE NULL END AS japanese
     FROM
       testscores ts1
     WHERE
       ts1.math = 'A'  AND ts1.japanese = 'A' AND ts2.student_id = ts1.student_id
 );

SELECT

ts2.*

FROM

testscores ts2

WHERE

EXISTS (

SELECT

ts1.*,

CASE WHEN subject = '算数' AND score >= 80 THEN 'A' ELSE NULL END AS math,

CASE WHEN subject = '国語' AND score >= 50 THEN 'A' ELSE NULL END AS japanese

FROM

testscores ts1

WHERE

ts1.math = 'A' AND ts1.japanese = 'A' AND ts2.student_id = ts1.student_id

);

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

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

SELECT
   ts2.*
 FROM
   testscores ts2
 WHERE
   EXISTS (
     SELECT
      *
     FROM
       testscores ts1
     WHERE
    1 =  CASE WHEN subject = '算数' AND score >= 80 THEN 1 END  
       AND
    1 = CASE WHEN subject = '国語' AND score >= 50 THEN 1 END
       AND 
       ts2.student_id = ts1.student_id
 );

SELECT

ts2.*

FROM

testscores ts2

WHERE

EXISTS (

SELECT

FROM

testscores ts1

WHERE

1 = CASE WHEN subject = '算数' AND score >= 80 THEN 1 END

AND

1 = CASE WHEN subject = '国語' AND score >= 50 THEN 1 END

AND

ts2.student_id = ts1.student_id

);

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

SELECT
   ts2.*
 FROM
   testscores ts2
 WHERE
   EXISTS (
     SELECT
      *
     FROM
       testscores ts1
     WHERE
    1 =  CASE WHEN subject = '算数' AND score >= 80 THEN 1  
         WHEN subject = '国語' AND score >= 50 THEN 1 ELSE NULL END
     AND 
     ts2.student_id = ts1.student_id
 );

SELECT

ts2.*

FROM

testscores ts2

WHERE

EXISTS (

SELECT

FROM

testscores ts1

WHERE

1 = CASE WHEN subject = '算数' AND score >= 80 THEN 1

WHEN subject = '国語' AND score >= 50 THEN 1 ELSE NULL END

AND

ts2.student_id = ts1.student_id

);

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

SELECT
   ts2.*
 FROM
   testscores ts2
 WHERE
   NOT EXISTS (
     SELECT
      ts1.subject 
     FROM
       testscores ts1
     WHERE
    1 =  CASE WHEN subject = '算数' AND score < 80 THEN 1  
         WHEN subject = '国語' AND score < 50 THEN 1 ELSE NULL END
     AND 
     ts2.student_id = ts1.student_id
 );

SELECT

ts2.*

FROM

testscores ts2

WHERE

NOT EXISTS (

SELECT

ts1.subject

FROM

testscores ts1

WHERE

1 = CASE WHEN subject = '算数' AND score < 80 THEN 1

WHEN subject = '国語' AND score < 50 THEN 1 ELSE NULL END

AND

ts2.student_id = ts1.student_id

);

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

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

SELECT
   ts2.student_id,
   COUNT(ts2.*)
 FROM
   testscores ts2
 WHERE
   ts2.subject IN ( '国語','算数')  AND
   NOT EXISTS (
     SELECT
      ts1.subject 
     FROM
       testscores ts1
     WHERE
    1 =  CASE WHEN subject = '算数' AND score < 80 THEN 1  
         WHEN subject = '国語' AND score < 50 THEN 1 ELSE NULL END
     AND 
     ts2.student_id = ts1.student_id
 )
GROUP BY ts2.student_id
HAVING  COUNT(*) = 2

SELECT

ts2.student_id,

COUNT(ts2.*)

FROM

testscores ts2

WHERE

ts2.subject IN ( '国語','算数') AND

NOT EXISTS (

SELECT

ts1.subject

FROM

testscores ts1

WHERE

1 = CASE WHEN subject = '算数' AND score < 80 THEN 1

WHEN subject = '国語' AND score < 50 THEN 1 ELSE NULL END

AND

ts2.student_id = ts1.student_id

)

GROUP BY ts2.student_id

HAVING COUNT(*) = 2

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

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