[DB] Oracle WITH(CTE) 구문절 VS Mysql WITH(CTE) 구문절 차이점

CTE란?

Common Table Expression 의 약어로, 일반적으로 공통 테이블 표현식이라고 번역됩니다.

sql 쿼리내에서 임시로 정의된 이름이 지정된 집합으로 간주되는 가상의 테이블로

복잡한 쿼리를 간결하게 작성하고, 재사용 가능한 부분을 정의하며 가독성을 향상시킬 수 있습니다.

 

이러한 CTE를 Oracle과 Mysql8.0 이상에서는 WITH 구문절로 제공하고 있습니다.

 

 

WITH(CTE) 구문절 사용법

WITH TABLE_NAME AS (
	SELEECT COL1,COL2
    FROM TABLE
    WHERE CONDITION
)

SELECT *
FROM TABLE_NAME
WHERE COL1 = VALUE;

 

위의 형식으로 ' WITH 테이블명 AS (SELECT ~) ' 의 형식으로 Oracle과 Mysql8.0 이상에서는 CTE를 사용하고 있습니다.

 

Oracle CTE vs Mysql CTE 차이점

사용법은 위에서 설명한것과 동일한 형태로 사용되지만, 일부 차이점이 존재합니다.

 

① RECURSIVE 키워드

 

MYSQL8.0 이상에서는 RECURSIVE 키워드를 제공하여 , 재귀 CTE를 보다 쉽게 구현 할 수 있습니다.

WITH 키워드 뒤에 RECURSIVE 키워드를 추가하면 파라미터 인자값을 지정하지 않고도,  재귀 CTE로 구현이 가능합니다.

WITH RECURSIVE org_hierarchy AS(
	SELECT org_id, org_name , parent_org_id, 0 AS level1
    FROM organizations
    WHERE org_id = 2
    
   UNION ALL
   
   SELECT o.org_id, o.org_name, o.parent_org_id, oh.level + 1
   FROM organizations o
   JOIN  org_hierarchy oh ON o.parent_org_id = oh.org_id
)

SELECT org_id, org_name, level
FROM org_hierarchy;

 

하지만 Oracle에서는 RECURSIVE 키워드를 제공하지 않기때문에 파라미터를 받아 구현해야됩니다.

 

 

② WITH 구문 내 ORDER BY 사용 여부

 

MySQL에서는 WITH 구문 내부에서 ORDER BY 절을 사용할 수 없지만, Oracle에서는 WITH 구문 내부에서 ORDER BY 절을 사용 할 수 있습니다.

Oracle이 WITH 구문 내부에서도 ORDER BY절을 사용할 수 있는 이유는 Oracle의 실행 계획 최적화 방식에서 ORDER BY 절도 고려하여 결과를 보장할 수 있도록 구성되어 있기때문입니다.