[MY SQL] 응시하지 않은 과목 0으로 표시하기

출처: [Leetcode - 1280. Students and Examinations

문제(일부):  학생정보(학생 id, 학생이름), 과목이름, 시험정보(학생id, 학생이름) 테이블을 사용, 
학생별로 응시한 과목의 시험 수 count하기

---

JOIN 사용하기

1) Students테이블과 Subjects 테이블을 조인조건 없이 JOIN, 카티션 곱으로 모든 학생에게 동일하게 과목을 모두 매칭
2)  LEFT JOIN으로 Examinations에 없는(=응시하지 않은 과목)도 count할 수 있게 함
3) 학생이름과 과목으로 groupby하여 count, 학생별 과목별 시험 수를 cnt

SELECT st.Student_id, st.Student_name, su.subject_name, count(e.subject_name) as attended_exams 
FROM Students AS st 
JOIN Subjects AS su 
LEFT JOIN Examinations AS e 
  ON st.student_id = e.student_id AND su.subject_name = e.subject_name
GROUP BY st.student_id, su.subject_name 
ORDER BY st.student_id, su.subject_name

WITH절 사용하기

1) WITH절에서 학생별 과목별 시험응시 수를 cnt
2) 1번과 동일하게 테이블을 join
3) 주의할 점은 응사하지 않은 과목에 대해 join 시 attended_exams에 null이 발생,
    coalesce를 사용하여 0이 될 수 있게 한다. 

WITH attended_exams AS(
    SELECT student_id, subject_name, count(subject_name) AS attended_exams
    FROM Examinations
    GROUP BY Student_id,subject_name 
)
SELECT st.student_id, st.student_name, su.subject_name, coalesce(e.attended_exams, 0) attended_exams
FROM Students AS st 
JOIN Subjects AS su 
LEFT JOIN attended_exams AS e 
  ON st.student_id = e.student_id AND su.subject_name = e.subject_name
ORDER BY st.student_id, su.subject_name