[MySQL] 서브쿼리와 인라인뷰 및 쿼리 실행 순서

𐂂 퍼포먼스(성능)

#1.
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = e.deptno
WHERE d.deptno = 10;

#2.
SELECT e.ename, d.dname
FROM emp e
JOIN (SELECT * 
      FROM dept
      WHERE deptno = 10) d ON e.deptno = d.deptno;

🪅#2가 퍼포먼스가 더 좋다/
WHY
emp 테이블의 레코드가 10개라고 하고,
dept 테이블의 레코드가 100개라고 했을 때,
#1은 emp 레코드 하나당 dept 레코드 100개를 검사해야하므로,
총 1000번의 비교연산이 들어간다.

#2는 인라인뷰에서 dept 레코드 100개 중 deptno = 10인 레코드를 뽑고(60개라 가정),
emp 레코드 하나당 dept 레코드 60개를 검사해야하므로,
총 700(100 + 10 * 60)번의 비교연산이 들어간다.

따라서, #2의 퍼포먼스가 더 좋다.

𖠃 인라인뷰

FROM절 or JOIN절에 오는 SELECT절을 '인라인뷰'라고 한다.
하나의 쿼리내에서, 서브쿼리를 사용하여 새로운 가상테이블을 생성하고,
기존에 존재하지 않았던 Column을 만들어
이를 주쿼리에서, 데이터 소스로 활용하는 것을 의미한다.

  SELECT deptno, SUM(bonus) AS "sum bonus" 
  FROM    (SELECT deptno, sal,
              CASE
                  WHEN 'job' = 'CLERK' THEN sal*1.1
                  WHEN 'job' = 'MANAGER' THEN sal*1.15
                  WHEN 'job' = 'ANALYST' THEN sal*1.20
                  ELSE 0
              END AS bonus
           FROM emp
           WHERE deptno IS NOT NULL) e
  GROUP BY deptno;

📕 참고 자료 📕

𐁍 서브쿼리

WHERE, HAVING, IN, NOT IN, EXISTS, NOT EXISTS절에 쓰이는 SELECT절을 '서브쿼리'라 한다.

🎯 서브쿼리를 쓰는 목적 중의 하나가 그룹함수이다.
(1) 특정그룹의 평균값보다 큰값을 찾을 때
(2) 그룹별로 순위매길 때
(3) 그룹내에서 조건을 만족하는 데이터를 찾을 때

𖠃 쿼리 실행 순서 ⭐⭐⭐

FROM -> ON -> WHERE -> SELECT -> GROUBY/집계함수 -> HAVING -> ORDER BY -> LIMIT

🤡 SELECT에서 Column명에 Alias붙인 것을 WHERE절에서는 모른다.
  (WHERE절이 SELECT절보다 먼저 실행되기 때문에)
🤡 FROM절에서 table명에 Alias붙인 것은 어느절에서나 사용할 수 있다.
  (FROM절이 가장 먼저 실행되기 때문에)

🏓 지역별 사원들의 평균연봉보다 급여가 높은 사원의 정보를 지역별로 출력하세요.
ex. 이름, 부서명, 지역명, 연봉등급, 연봉, 지역별 평균연봉

  SELECT e.ename, d.dname, d.loc, s.grade, e.sal, l.loc_avgsal
  FROM emp e
  LEFT OUTER JOIN dept d ON e.deptno = d.deptno
  JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
  JOIN (SELECT d2.loc, AVG(e2.sal) AS loc_avgsal
          FROM emp e2
          JOIN dept d2 ON e2.deptno = d2.deptno
          GROUP BY d2.loc) l ON d.loc = l.loc
  WHERE e.sal > l.loc_avgsal
  ORDER BY d.loc;

🏓 입사년도별 사원들의 평균연봉보다 급여가 높은 사원의 정보를 입사년도별로 출력하세요.
ex. 이름, 입사년도, 연봉, 입사년도별 평균연봉

  SELECT e.ename, YEAR(e.hiredate) AS hire_year, e.sal, hs.hire_avgsal AS avgsal
  FROM emp e
  JOIN (SELECT YEAR(e2.hiredate) AS hs_year, AVG(e2.sal) AS hire_avgsal
           FROM emp e2
          GROUP BY YEAR(hiredate)) hs ON e.hire_year = hs.hs_year
  WHERE e.sal > hs.hire_avgsal
  ORDER BY e.hire_year;

🏓 부서위치가 'CHICAGO'인 사원의 평균연봉보다 급여가 높은 사원의 정보를 출력하세요.
ex. 이름, 부서명, 연봉

  SELECT e.ename, d.dname, e.sal
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.sal > (SELECT AVG(e2.sal)
                   FROM emp e2
                   WHERE deptno  = (SELECT deptno FROM dept WHERE loc = 'CHICAGO'));