SQL 정리(Join - cross, left)(leetcode - 1280. Students and Examinations)(23.12.28)

문제

https://leetcode.com/problems/students-and-examinations/description/

Students and Examinations - LeetCode

 

정답쿼리

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

 

LEFT JOIN Examinations c ON a.student_id = c.student_id AND b.subject_name = c.subject_name

 

Q. left join에 and가 들어가는 이유가 무엇일까??

A. left join에 and가 들어가는 이유는 한 번의 left join만으로는 원하는 값을 출력할 수 없기 때문이다.

 

Q. 원하는 값은 무엇인가??

A. 학생별로 참석한 과목별 시험의 개수

 

원하는 값이 도출되는 과정을 테이블을 하나하나 JOIN시키면서 살펴보자

 

1. 테이블 살펴보기

테이블의 종류는 Students, Subject, Examination 이렇게 세가지이다.

 

2. Students 테이블과 Subject 테이블을 cross join하기

cross join은 두 테이블이 join되는 모든 경우의 수를 계산하기때문에

파란색 D와같은 테이블이 나오게 된다.

 

3. cross join된 테이블과 examination 테이블을 left join

left join을 할 때 필요한 조건은 student_id(이미지에서는 id로 표시)와 subject_name(c테이블에서는 name으로 표시) 두가지이다.

원래대로라면 두 가지 조건이 한 번에 적용되는데 이해를 위해 student_id가 적용됐을때와 subject_name이 적용됐을 때를 나눠서 생각해보자.

 

1) student_id를 기준으로 left join

 

student_id가 기준이 됐을때의 left join이다.

위 이미지를 보면 D테이블의 첫번째 행에 C테이블의 id가 1인 애들이 모두 붙는다.

작업이 끝나면 첫번째와 만찬가지로 D테이블의 두번째 행에 C테이블의 id가 1인 애들이 모두 붙는다.

그렇게 반복했을때의 결과값은 아래와 같다.

 

2) subject_name을 기준으로 left join

이번에는 subject_name이라는 기준도 함께 포함해서 left join을 해보자.

student_id를 기준으로 left join했을때는 student_id가 같기만하면 그 값에 해당하는 모든 값들이 붙었지만

subject_name이라는 기준도 함께 고려해야할때는 D테이블과 C테이블에서 student_id와 subject_name이 모두 동일한 애들만 붙게된다.

다른 행에도 마찬가지로 join해주면 결과값은 아래와 같이 출력된다.

---

 

문제를 이해하는 과정에서는 테이블에 어떤 속성과 속성값들이 들어가는지 확인하기위해서 select에 * 을 넣고 돌렸는데

실제로는 student_id, student_name, 그리고 attended_exams(응시한 시험의 수)가 출력돼야하며

attended_exams의 경우 count함수를 활용하여 구할 수 있다.

이때 count( ) 안에 a테이블의 subject_id가 아닌 c테이블의 subject_id(c.subject_id)를 넣어줘야

문제에서 요구하는 답을 출력할 수 있다.