DBMS Day13 SUBQUERY, RIGHT JOIN, INNER JOIN

-> LEFT JOIN 을 했을 때

SELECT `teacher`.`id` AS `교번`,
       `teacher`.`name` AS `교사명`,
       `teacher`.`designated_grade` AS `담임 학년`,
       `teacher`.`designated_class` AS `담임 반`,
       COUNT(0) AS `담당 학생 수`
FROM `school`.`teachers` AS `teacher`
    LEFT JOIN `school`.`students` AS `student` ON `teacher`.`designated_grade` = `student`.`grade` AND `teacher`.`designated_class` = `student`.`class`
GROUP BY `teacher`.`id`
ORDER BY `teacher`.`id`;

RIGHT JOIN

student 에 0학년 0반이 없기 때문에 사라진다.

RIGHT JOIN `school`.`students` AS `student` ON `teacher`.`designated_grade` = `student`.`grade` AND `teacher`.`designated_class` = `student`.`class`

INNER JOIN

 INNER JOIN `school`.`students` AS `student` ON `teacher`.`designated_grade` = `student`.`grade` AND `teacher`.`designated_class` = `student`.`class`

A LEFT JOIN B
A RIGHT JOIN B
A INNER JOIN B

SELECT `teacher`.`id` AS `교번`,
       `teacher`.`name` AS `교사명`,
       `teacher`.`designated_grade` AS `담임 학년`,
       `teacher`.`designated_class` AS `담임 반`,
       COUNT(0) AS `담당 학생 수`,
       AVG(`score`.`score`) AS `학생 평균`
FROM `school`.`teachers` AS `teacher`
    LEFT JOIN `school`.`students` AS `student` ON `teacher`.`designated_grade` = `student`.`grade` AND `teacher`.`designated_class` = `student`.`class`
        LEFT JOIN `school`.`scores` AS `score` ON `student`.`grade` = `score`.`student_grade` and `student`.`class` = `score`.`student_class` and `student`.`number`   =`score`.`student_number`
GROUP BY `teacher`.`id`
ORDER BY `teacher`.`id`;

• 학생 수가 중간, 기말 시험 2개가 있기 때문에 명수가 6명이 나오게 된다.

SUBQUERY

SELECT 하는 열의 갯수는 하나여야한다.

• 서브쿼리를 많이 쓰는건 좋지 않다.
  SELECT를 한번 실행하는데 서브 SELECT를 9번 실행한 것 처럼 성능 저하가 일어나기 때문이다.

• 서브쿼리 실용 예제 가상의 열을 만들어서 사용도 가능하다.

SELECT `sub`.`value`     AS `숫자`,
       `join_sub`.`name` AS `한글`
FROM (SELECT 1 AS `value`) AS `sub`
         LEFT JOIN (SELECT 1 AS `value`, '일' AS `name`) AS `join_sub`
                   ON `sub`.`value` = `join_sub`.`value`;

SELECT *
FROM
(SELECT `teacher`.`id`                                           AS `교번`,
       `teacher`.`name`                                         AS `교사명`,
       `teacher`.`designated_grade`                             AS `담임 학년`,
       `teacher`.`designated_class`                             AS `담임 반`,
       (SELECT COUNT(0)
        FROM `school`.`students` AS `student`
        WHERE `student`.`grade` = `teacher`.`designated_grade`
          AND `student`.`class` = `teacher`.`designated_class`) AS `담당 학생 수`,
       AVG(`score`.`score`)                                     AS `학생 평균`
FROM `school`.`teachers` AS `teacher`
         LEFT JOIN `school`.`scores` AS `score` ON `teacher`.`designated_grade` = `score`.`student_grade` AND
                                                   `teacher`.`designated_class` = `score`.`student_class`
GROUP BY `teacher`.`id`
ORDER BY `teacher`.`id`) AS `sub`
ORDER BY `sub`.`담임 학년`, `sub`.`담임 반`;