[SQL][SQLD][SQLP] WITH절 (일반, Recursive, 계층)

WITH절

SQL을 작성하다 보면 반복되는 서브 쿼리를 이곳저곳 복붙 해서 사용해야 하는 경우가 있다. 이곳저곳 흩어져 있기 때문에 가독성도 떨어지고, 만약 서브 쿼리를 수정해야 한다면 위치를 찾아 각각 다 고쳐줘야 한다.

 

WITH 절은 이러한 서브 쿼리의 단점을 해결하기 위해 서브 쿼리를 마치 함수처럼 이름을 가지도록 사용하는 방법이다.

 

약간 VIEW와 쓰임새가 비슷해보이지만 차이점은 WITH 절은 VIEW처럼 DBMS에 저장하여 사용하는 것이 아닌 실행할 쿼리문 내에 정의되어 있어, 해당 쿼리가 실행될 때마다 파싱 되고 실행계획을 세운다.

 

 

 

WITH절 기본

WITH절의 기본 구조 예제는 아래와 같다.

WITH
WITH_SUB AS (
	... -- 서브쿼리 작성
)
SELECT * FROM WITH_SUB; -- WITH절을 사용한 메인 쿼리 예시

 

WITH절은 여러 개 선언할 수 있으며, WITH절에서 다른 WITH 절을 사용할 수 있다.

WITH
WITH_SUB1 AS (
	... -- 서브쿼리 작성
),
WITH_SUB2 AS (
	SELECT * FROM WITH_SUB1 -- 다른 WITH절을 사용한 WITH절 
)
SELECT * FROM WITH_SUB2;

 

 

Recursive WITH절

WITH절을 가지고 자신이 자신을 호출하는 Recursive 한 방식을 사용할 수 있다. 이를 Recursive WITH절이라고 한다.

 

Recursive WITH 절을 사용하려면 UNION ALL을 가지고 초기값을 정해주는 초기 서브 쿼리와, 이후에 행위를 작성하는 Recurisive 서브 쿼리로 나뉜다. 단 항상 재귀함수에서 그랬듯이 무한루프에 빠지지 않도록 종료조건을 조건절로 표현을 해야한다.

 

아래는 1~9까지의 합을 구하는 예제이다.

WITH
SUM(NUM, RESULT) AS(
	-- 초기값을 설정해주는 초기 서브쿼리이다. 즉 NUM=1, RESULT=1 이다.
	SELECT 1,1 FROM DUAL 
	UNION ALL
	-- 이후 행위를 정의한 Recurisive 서브쿼리이다. NUM의 값은 1씩 증가시키며, RESULT 값에는 계속 (NUM + 1)을 더한다.
	SELECT NUM+1, (NUM+1) + RESULT FROM SUM WHERE NUM < 9-- 종료조건으로 NUM < 9를 작성했다.
)
SELECT NUM ,RESULT FROM SUM;

 

 

계층형 WITH절

오라클에서는 계층형 쿼리를 위해 CONNECT BY문을 지원한다. 하지만 이 문법은 오라클에서만 사용 가능하다. WITH절은 ANSI SQL에 포함되어 있기 때문에 여러 DBMS에서 지원한다. 따라서 WITH절은 DBMS가 변경되어도 쿼리를 변경할 필요가 없다는 장점이 있기때문에 계층형 쿼리를 작성할 때는 CONNECT BY보다는 WITH절을 사용하자.

 

아래 예제는 WITH절을 이용한 계층형 쿼리 예제이다.

WITH
WITH_TAB (LVL, NAME, EMP_ID, MGR_ID) AS
(
  -- 초기값을 설정해주는 초기 서브쿼리이다. 즉 LVL=1이고, MGR_ID가 NULL인 값이 최초 값이다.
  SELECT 1, NAME, EMP_ID, MGR_ID
  FROM HR.EMPLOYEES
  WHERE MGR_ID IS NULL -- 오라클의 START_WITH와 동일한 조건
  UNION ALL
  -- 이후 행위를 정의한 Recurisive 서브쿼리이다. 초기값의 EMP_ID의 값이 MGR_ID와 같은 행을 찾아 LVEL + 1을 해준다.
  SELECT B.LVL + 1 AS LVL, A.NAME, A.EMP_ID, A.MGR_ID
  FROM HR.EMPLOYEES A, WITH_TAB B
  WHERE A.MGR_ID = B.EMP_ID -- 오라클의 CONNECT BY PRIOR와 동일한 조건
)
SELECT LVL, NAME, EMP_ID, MGR_ID FROM WITH_TAB;

 

아래는 위와 같은 결과를 나타내는 오라클의 계층형 쿼리이다.

SELECT LEVEL, NAME, EMP_ID, MGR_ID
FROM HR.EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;