SQL初心者がサブクエリ使ってみた。

きっかけ

業務で下記のようなテーブルがあって、欲しい値が取れず苦労したのですが
サブクエリを使用したら取得できたので記録。

subjectテーブル

id	subject
1	国語
2	数学
3	英語
4	理科
5	社会

teacherテーブル

id	name
1	yamada
2	shimizu
3	sato

teacher_subjectテーブル

id	teacher_id	subject_id
1	1	1
2	1	3
3	2	1
4	3	2
5	3	4
6	3	3

国語と英語ができる先生を知りたいとき

試したこと1

select teacher.name
    from teacher_subject
        left join teacher on teacher.id = teacher_subject.teacher_id
        left join subject on subject.id = teacher_subject.subject_id
        where teacher_subject.subject_id = 1 and teacher_subject.subject_id = 3;　 //-①

→１件もヒットしない。
　原因はwhereの中。
　1レコード上にsubject_idが2つあることはありえないので1件もヒットしない。

試したこと2

select teacher.name
    from teacher_subject
        left join teacher on teacher.id = teacher_subject.teacher_id
        left join subject on subject.id = teacher_subject.subject_id
        join (select count(*) as cnt, teacher_id //-②
            from teacher_subject
            where subject_id in (1, 3)
            group by teacher_id) as rd on rd.cnt = 2 and rd.teacher_id = teacher.id group by teacher.name;

→無事山田さんを取得できた。

解説

試したこと1で失敗したけどやりたかったことは、teacher_subjectテーブルからsubject_id=1(国語)と、subject_id=3(英語)の両方があるteacher_idを取得し、teacherテーブルのnameを取得したかった。（①）

なので、それを実現するために追加したのが、試したこと2の5~9行目（②）。
teacher_subjectテーブルにsubject_id=1かsubject_id=3があるteacher_idを取得し、teacher_idでgroup byしカウントする。
（カウントしたものをcntに入れる）

subject_id=1とsubject_id=3の両方がないといけないので、カウントした数(cnt)が2となり、該当したteacher_idから名前を取得する。