주어진 문제 : https://leetcode.com/problems/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 |
위 테이블을 이용해서 각 학생들이 시험을 본 횟수를 나타내고 싶다.
# 만들고 싶은 결과물
| student_id | student_name | subject_name | attended_exams |
| ---------- | ------------ | ------------ | -------------- |
| 1 | Alice | Math | 3 |
| 1 | Alice | Physics | 2 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Physics | 0 | * 0이라도 표시!
| 2 | Bob | Programming | 1 |
| 6 | Alex | Math | 0 | * 0이라도 표시!
| 6 | Alex | Physics | 0 | * 0이라도 표시!
| 6 | Alex | Programming | 0 | * 0이라도 표시!
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
그냥 LEFT JOIN을 써서 COUNT하면 0인값이 출력되지 않는다.
# 오류1: 0인 값들이 출력되지 않음
SELECT s.student_id, s.student_name, subject_name, COUNT(*) AS attended_exams
FROM Students s
LEFT JOIN Examinations e ON s.student_id = e.student_id
GROUP BY s.student_id, subject_name
ORDER BY s.student_id, subject_name
# 출력값
| student_id | student_name | subject_name | attended_exams |
| ---------- | ------------ | ------------ | -------------- |
| 1 | Alice | Math | 3 |
| 1 | Alice | Physics | 2 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Programming | 1 |
| 6 | Alex | null | 1 |
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
이 때 CROSS JOIN을 쓰면 가능하다
CROSS JOIN(상호 조인) : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능
이를 이용하여
1. Students 테이블과 Subjects 테이블을 조인하여 각 학생별 모든 과목을 나타내는 테이블을 먼저 만들고
2. 결과 값과 Examination 테이블을 합쳐서 COUNT한다.
# 과정 1: 각 학생별 모든 과목을 나타내는 테이블 만들기
SELECT *
FROM Students s
CROSS JOIN Subjects sub
# 출력값
| student_id | student_name | subject_name |
| ---------- | ------------ | ------------ |
| 1 | Alice | Programming |
| 1 | Alice | Physics |
| 1 | Alice | Math |
| 2 | Bob | Programming |
| 2 | Bob | Physics |
| 2 | Bob | Math |
| 13 | John | Programming |
| 13 | John | Physics |
| 13 | John | Math |
| 6 | Alex | Programming |
| 6 | Alex | Physics |
| 6 | Alex | Math |
# 과정2 : 결과 값과 Examination 테이블을 합치기(LEFT JOIN)
SELECT *
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
# 출력값
| student_id | student_name | subject_name | student_id | subject_name |
| ---------- | ------------ | ------------ | ---------- | ------------ |
| 1 | Alice | Programming | 1 | Programming |
| 1 | Alice | Physics | 1 | Physics |
| 1 | Alice | Physics | 1 | Physics |
| 1 | Alice | Math | 1 | Math |
| 1 | Alice | Math | 1 | Math |
| 1 | Alice | Math | 1 | Math |
| 2 | Bob | Programming | 2 | Programming |
| 2 | Bob | Physics | null | null |
| 2 | Bob | Math | 2 | Math |
| 13 | John | Programming | 13 | Programming |
| 13 | John | Physics | 13 | Physics |
| 13 | John | Math | 13 | Math ...
마지막으로 학생, 과목별 GROUP BY 조건과 ORDER BY 조건을 추가해주고
SELECT문에서 COUNT로 집계하면... 완성!
# 완성코드
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;
# 출력값
| student_id | student_name | subject_name | attended_exams |
| ---------- | ------------ | ------------ | -------------- |
| 1 | Alice | Math | 3 |
| 1 | Alice | Physics | 2 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Physics | 0 |
| 2 | Bob | Programming | 1 |
| 6 | Alex | Math | 0 |
| 6 | Alex | Physics | 0 |
| 6 | Alex | Programming | 0 |
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
[참고]
