not existsを扱う

以下のテーブルがある。

select * from testscores;

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

全ての教科で50点以上取っている生徒を取得する。

select
distinct student_id
from
testscores ts1
where
not exists
(
    select
    *
    from
    testscores ts2
    where
    ts1.student_id = ts2.student_id
    and ts2.score < 50
)

+------------+
| student_id |
+------------+
|        100 |
|        200 |
|        400 |
+------------+

次は算数が80点以上、国語が50点以上の生徒を取得する。

select
distinct student_id
from
testscores ts1
where
subject in('算数','国語')
and not exists
(
    select
    *
    from
    testscores ts2
    where
    ts1.student_id = ts2.student_id
    and 1 =
    case when subject = '算数' and score < 80 then 1
    when subject = '国語' and score < 50 then 1
    else 0 end
)

+------------+
| student_id |
+------------+
|        100 |
|        200 |
|        400 |
+------------+

上記のSQLでは、国語のデータが存在しないstudent_id400の生徒が取得されてしまっている。
それを除外したい場合、行数を数えるhaving句を追加すればいい。

select
student_id
from
testscores ts1
where
subject in('算数','国語')
and not exists
(
    select
    *
    from
    testscores ts2
    where
    ts1.student_id = ts2.student_id
    and 1 =
    case when subject = '算数' and score < 80 then 1
    when subject = '国語' and score < 50 then 1
    else 0 end
)
group by student_id
having count(*) = 2

+------------+
| student_id |
+------------+
|        100 |
|        200 |
+------------+

こちらを参考にさせていただきました。
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ