[MySQL] CTE 예시 쿼리

CTE 관련 쿼리를 학습 중 보면 좋은 예제가 있어 첨부한다. 

 

EXPLAIN FORMAT=TREE 
WITH 
	cte1 AS (
		SELECT emp_no, MIN(from_date) AS salary_from_date 
        FROM salaries 
        WHERE salary BETWEEN 50000 AND 51000 
        GROUP BY emp_no 
    ), 
    cte2 AS (
		SELECT de.emp_no, MIN(from_date) AS dept_from_date 
        FROM cte1 
			INNER JOIN dept_emp de ON de.emp_no=cte1.emp_no 
        GROUP BY emp_no 
    )
SELECT * FROM employees e 
	INNER JOIN cte1 t1 ON t1.emp_no=e.emp_no
    INNER JOIN cte2 t2 ON t2.emp_no=e.emp_no;

 

이에 대한 EXPLAIN 결과는 아래와 같다. 

 

-> Nested loop inner join
    -> Nested loop inner join
        -> Table scan on t1
            -> Materialize CTE cte1 if needed
                -> Table scan on <temporary>
                    -> Aggregate using temporary table
                        -> Filter: (salaries.salary between 50000 and 51000)  (cost=9267.18 rows=46022)
                            -> Index range scan on salaries using ix_salary  (cost=9267.18 rows=46022)
        -> Single-row index lookup on e using PRIMARY (emp_no=t1.emp_no)  (cost=0.72 rows=1)
    -> Index lookup on t2 using <auto_key0> (emp_no=t1.emp_no)
        -> Materialize CTE cte2
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Nested loop inner join
                        -> Table scan on cte1
                            -> Materialize CTE cte1 if needed (query plan printed elsewhere)
                        -> Index lookup on de using ix_empno_fromdate (emp_no=cte1.emp_no)  (cost=1.00 rows=1)