SQL | LeetCode 1280. Nested Join

학교에는 여러 명의 학생들과 여러 과목이 있고, 각 학생은 모든 과목의 시험을 치릅니다. 우리는 각 학생이 각 과목의 시험을 몇 번 봤는지 알고 싶습니다. 

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.

 

문제는 모든 학생이 모든 과목 시험을 몇 번 봤는지를 묻고 있고, 시험을 한 번도 안 본 경우도 0으로 나와야 합니다. INNER JOIN은 매칭된 행만 남기기 때문에, 시험을 한 번도 안 본 경우는 아예 사라집니다. 핵심은 모든 학생 × 모든 과목 조합을 만든 뒤, Examinations에서 몇 번 시험을 본 적이 있는지를 세는 겁니다.. 이건 흔히 Many-to-Many 관계에서의 카운팅 + OUTER JOIN을 묻는 문제로, 실무에서도 꽤 자주 등장합니다.

SELECT * FROM Students CROSS JOIN Subjects;

| 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         |

 

모든 학생 × 모든 과목 조합 생성을 합니다.

SELECT * FROM Students s 
    CROSS JOIN Subjects ss
    LEFT JOIN Examinations e ON s.student_id = e.student_id AND ss.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         |
| 6          | Alex         | Programming  | null       | null         |
| 6          | Alex         | Physics      | null       | null         |
| 6          | Alex         | Math         | null       | null         |

시험 친 기록이 없어도 학생과 과목 정보는 남게 됩니다.

SELECT s.student_id, s.student_name, ss.subject_name, COUNT(e.student_id) AS attended_exams FROM Students s 
    CROSS JOIN Subjects ss
    LEFT JOIN Examinations e ON s.student_id = e.student_id AND ss.subject_name = e.subject_name
    GROUP BY s.student_id, ss.subject_name
    ORDER BY s.student_id, ss. subject_name;

시험 안 본 경우도 포함해서 0을 리턴합니다.