[리트코드] Easy|1280. Students and Examinations; CROSS JOIN
https://leetcode.com/problems/students-and-examinations/description/
1. Problem
학교의 모든 학생이 모든 과목에 대해 몇 번의 시험을 치렀는지 집계해야 한다.
- 핵심 난관: 시험 기록(Examinations)이 전혀 없는 학생이나, 특정 과목을 응시하지 않은 경우에도 결과 테이블에는 **'0'**으로 표시되어 반드시 등장해야 한다.
2. Solution: 카테시안 곱과 외부 조인의 결합
모든 학생과 모든 과목의 조합을 CROSS JOIN으로 선언하여 '응시 가능한 전체 목록'을 만든 뒤, 실제 응시 기록을 결합한다.
3. Takeaway: 왜 CROSS JOIN이 필수적이었나? (객관적 분석)
- INNER JOIN의 한계: 유진 님의 오답처럼 Students와 Examinations를 일반 조인하면, **시험을 한 번도 안 본 학생(Alex)**은 결과에서 아예 증발해 버린다. 데이터 분석에서 '기록 없음(0)'은 '존재하지 않음'과 엄격히 구분되어야 한다.
- 데이터 틀(Template) 만들기: CROSS JOIN은 행과 행 사이에 아무런 조건 없이 모든 경우의 수를 곱한다. 이를 통해 "이 학생은 이 과목을 볼 수도 있었다"는 논리적 기반을 먼저 마련하는 것이 이 문제의 가장 날카로운 포인트다.
- COUNT(*) vs COUNT(column):
- COUNT(*)는 LEFT JOIN 후 매칭되는 데이터가 없어 NULL이 붙은 행도 '행 자체가 존재하므로' 1로 세어버릴 위험이 있다.
- **COUNT(e.subject_name)**은 컬럼 값이 NULL인 경우 집계에서 제외하므로, 응시하지 않은 과목을 정확히 0으로 표현할 수 있다.
Table: Students
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.
Table: Subjects
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.
Table: Examinations
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| student_id | int |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
Write a solution to find the number of times each student attended each exam.
Return the result table ordered by student_id and subject_name.
The result format is in the following example.
Example 1:
Input:
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math |
| Physics |
| Programming |
+--------------+
Examinations table:
+------------+--------------+
| 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 |
+------------+--------------+
Output:
+------------+--------------+--------------+----------------+
| 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 |
+------------+--------------+--------------+----------------+
Explanation:
The result table should contain all students and all subjects.
Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
Alex did not attend any exams.
John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.
1. 답안 쿼리
SELECT
s.student_id,
s.student_name,
sub.subject_name,
COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e # 조인조건1
ON s.student_id = e.student_id # 조인조건2
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;
- 1. Each student takes every course ---> CROSS JOIN시험 기록은 없을 수도 있음
Students × Subjects
- 시험 안 본 경우도 0으로 나와야 함
- 그래서 INNER JOIN ❌
- LEFT JOIN ⭕
LEFT JOIN Examinations
COUNT(e.subject_name)
- 시험 기록이 없으면 NULL
- COUNT(NULL) → 0
- 자동으로 안 본 시험은 0 처리됨
| student | subject | exam |
| Alice | Math | Math |
| Alice | Math | Math |
| Alice | Math | Math |
| Bob | Physics | NULL |
한 줄 요약 💡
- 전체 조합 필요 → CROSS JOIN : 학생 × 과목을 만들었으면 → 시험 기록은 학생 + 과목 둘 다로 매칭해야 한다
- 없는 데이터도 필요 → LEFT JOIN
- 개수 세기 → COUNT(column)
2. 나의 오답
SELECT s.student_id, s.student_name, e.subject_name, COUNT(*) AS attended_exams
FROM Students s
JOIN Examinations e on s.student_id = e.student_id
GROUP BY s.student_id, s.student_name, e.subject_name
ORDER BY s.student_id, e.subject_name;