[SQL] CROSS JOIN과 LEFT JOIN으로 “모든 학생 × 모든 과목” 응시 횟수 구하기

SQL 문제 중 아주 자주 등장하는 패턴이 있다.
“모든 학생과 모든 과목을 기준으로, 각 학생이 과목별 시험을 몇 번 봤는가?”

이 문제를 풀기 위해서는 CROSS JOIN과 LEFT JOIN, 그리고 COUNT() 함수의 동작 원리를 정확히 이해해야 한다.
아래는 단계별로 쿼리 실행 흐름과 실제 예시 결과를 보여준다.

---

🧩 0. 문제 데이터

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.subject_name) AS attended_exams   -- NULL은 세지지 않음
FROM Students AS s
CROSS JOIN Subjects AS sub
LEFT JOIN Examinations AS e
  ON e.student_id = s.student_id
 AND e.subject_name = sub.subject_name
GROUP BY
    s.student_id, s.student_name, sub.subject_name
ORDER BY
    s.student_id, sub.subject_name;

---

🧮 1. CROSS JOIN이란?

CROSS JOIN은 두 테이블의 데카르트 곱(Cartesian Product)을 만든다.
즉, A 테이블의 모든 행 × B 테이블의 모든 행을 조합하는 것.

우리의 경우에는 “모든 학생 × 모든 과목” 조합을 만들어야 하므로 딱 맞다.
학생이 4명, 과목이 3개라면 4 × 3 = 12행 이 생성된다.

student_id	student_name	subject_name
1	Alice	Math
1	Alice	Physics
1	Alice	Programming
2	Bob	Math
2	Bob	Physics
2	Bob	Programming
6	Alex	Math
6	Alex	Physics
6	Alex	Programming
13	John	Math
13	John	Physics
13	John	Programming

---

🔗 2. LEFT JOIN으로 시험 기록 붙이기

LEFT JOIN을 사용해 Examinations 테이블의 기록을 붙인다.
이때, 매칭이 안 되는 조합(시험을 안 본 경우)은 NULL로 남는다.

LEFT JOIN Examinations AS e
  ON e.student_id = s.student_id
 AND e.subject_name = sub.subject_name

💡 예시 중간 결과 (일부)

Alice (student_id = 1)

student_id	subject_name	e.subject_name
1	Math	Math
1	Math	Math
1	Math	Math
1	Physics	Physics
1	Physics	Physics
1	Programming	Programming

Bob (student_id = 2)

student_id	subject_name	e.subject_name
2	Math	Math
2	Physics	NULL
2	Programming	Programming

Alex (student_id = 6)

student_id	subject_name	e.subject_name
6	Math	NULL
6	Physics	NULL
6	Programming	NULL

---

🧾 3. GROUP BY + COUNT(e.subject_name)

학생·과목별로 묶어서 응시 횟수를 센다.
COUNT(e.subject_name)는 NULL을 세지 않기 때문에 응시하지 않은 조합은 0으로 계산된다.

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

---

📊 4. ORDER BY로 보기 좋게 정렬

결과를 student_id, subject_name 순으로 정렬하면 완성된다.

---

⚙️ 정리 요약

단계	사용 이유
CROSS JOIN	모든 학생 × 모든 과목 조합 생성
LEFT JOIN	응시 기록을 붙이되, 응시 안 해도 행 유지
COUNT(e.subject_name)	NULL은 세지 않아 0회 처리 가능
GROUP BY	학생·과목별로 집계
ORDER BY	결과 정렬

---

🚀 참고: 다른 방법 (CTE로 먼저 집계)

WITH exam_cnt AS (
  SELECT student_id, subject_name, COUNT(*) AS cnt
  FROM Examinations
  GROUP BY student_id, subject_name
)
SELECT
  s.student_id,
  s.student_name,
  sub.subject_name,
  COALESCE(ec.cnt, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN exam_cnt ec
  ON ec.student_id = s.student_id
 AND ec.subject_name = sub.subject_name
ORDER BY s.student_id, sub.subject_name;

TL;DR

“모든 조합을 CROSS JOIN으로 만들고, LEFT JOIN으로 기록을 붙이고 NULL 안 세는 COUNT로 집계하면 0회까지 정확하게 표현된다!”