MySQL CTE 사용 하기

MySQL CTE 사용하기

1. CTE란?

  • 정의는 common table expresion(CTE)으로 임시로 쿼리 결과를 저장해 놓고, 여러번 참조해서 사용하는 용도로 사용한다.
  • derived table(파생 테이블)과 비슷하게, 객체로 저정하지 않고, 쿼리가 실행되는 중에만 참조해서 사용한다.
  • 반면에 파생테이블과 달리, CTE는 자기 자신을 self참조 할 수 있고, 여러번 호출되어서 사용할 수 있다.

1.1. 파생테이블(derived table) 은 무엇?

  • 가상 테이블로 SELECT 문의 결과로 리턴한다. 다음과 같이 FROM절에서 SELECT문이 리턴하는게 파생 테이블이다.
  • 흔히 derived table은 subquery랑 상호 교환이 되기도 한다.
    • 하지만 derived table은 반드시 alias를 지정해야 한다.
    • 그렇지 않으면 Error Code: 1248. Every derived table must have its own alias라는 오류가 난다.

https://www.mysqltutorial.org/wp-content/uploads/2017/07/MySQL-Derived-Table.png

참고: https://www.mysqltutorial.org/mysql-cte/

2. CTE 문법

실제 CTE문법은 다음과 같다. WITH 키워드와 AS 로 cte_name에 매핑할 쿼리를 작성하고, 그 다음에 그 cte_name으로 지정한 테이블을 조회한다.

WITH cte_name (column_list) AS (
  	query
)
select * FROM cte_name; 

다음과 같이 쉼표로 이어서 계속해서 CTE로 만들어서 참조해서 사용이 가능하다.

WITH cte_name (column_list) AS (
  	query
), other_name (column_list) AS (
  	otherQuery
)
select * FROM cte_name; 

3. CTE 간단한 예제

WITH customers_in_usa AS (
  -- 1) 내부
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) 
-- 2) 외부 
SELECT customerName FROM customers_in_usa WHERE state = 'CA' ORDER BY customerName;

먼저 AS 안에 있는 쿼리( 내부 ) 부터 분석해보자. 비교적 간단한 쿼리다. customers 테이블에서 나라가 USA인 애들만 customerName과 state를 컬럼 리스트로 CTE_NAME 값 (customers_in_usa)에 매핑했다.

참고로, AS 절 안에있는 컬럼 리스트로 내보내는 컬럼들만이, 실제 외부에 있는 쿼리 작성할때 참조할 수 있다.

그리고 외부에 있는 쿼리 (외부)를 살펴보자. FROM 이후 테이블이 내부쿼리를 실행한 customers_in_usa 테이블에서 주가 CA(캘리포니아)인 customerName를 조회한다.

4. 정리

실제 CTE를 여기 회사에서 처음 써보긴 했다. 생각보다 복잡한 쿼리 (임시테이블을 참조해서 join하고 결과를 보여주는..등등)의 경우에 많이 쓰인다.

5. 참고