[SQL] CROSS JOIN 활용

👩‍🏫문제

1280. Students and Examinations
각 학생들이 각 과목을 응시한 횟수를 반환하라.

Students
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
+------------+--------------+

Subjects
+--------------+
| subject_name |
+--------------+
| Math |
| Physics |
| Programming |
+--------------+

Examinations
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1 | Math |
| 1 | Physics |
| 1 | Programming |
| 2 | Programming |
| 1 | Physics |
| 1 | Math |
| 13 | Math |
| 13 | Programming |
| 13 | Physics |
| 2 | Math |
| 1 | Math |
+------------+--------------+

👩‍🏫풀이

SELECT S.student_id, 
       S.student_name, 
       sub.subject_name, 
       COUNT(E.student_id) AS attended_exams
FROM Students S
CROSS JOIN Subjects sub
LEFT JOIN Examinations E
    ON S.student_id = E.student_id
    AND sub.subject_name = E.subject_name
GROUP BY S.student_id, S.student_name, sub.subject_name
ORDER BY S.student_id, sub.subject_name

💡 IDEA

• CROSS JOIN으로 접점이 없는 Students와 Subjects의 가능한 모든 수를 연결 (해당 학생이 응시하지 않은 과목은 '0'으로 표기해야 함)
• 여기서 LEFT JOIN으로 조인해야 student_id기준으로 정렬됨.

  LEFT JOIN Examinations E
  ON S.student_id = E.student_id

• 여기서 LEFT JOIN으로 조인해야 subject_name기준으로 정렬됨.

  LEFT JOIN Examinations E
  ...
  AND sub.subject_name = E.subject_name

하지만 여전히 어렵다...