데이터베이스 WITH절과 CTE

 

WITH절과 CTE

WITH 절은 CTE를 표현하기 위한 구문이다. CTE는 ANSI-SQL99 표준에서 나온것이다. 
CTE는 비재귀적과 재귀적으러 나뉜다. 재귀적은 잘사용되지않으니 비재귀적으로 배워보자

 

비재귀적 CTE는 말그대로 재귀적이지 않는 CTE다. 
단순한 형태이며, 복잡한 쿼리 문장을 단순화 시키는 데 적합하게 사용될 수 있다.

형식

WITH CTE_테이블이름(열이름)
AS
(
   <쿼리문>
)
SELECT 열이름 FROM CTE_테이블 이름;

select말고 update 등도 가능하지만 주로 사용되는것은 select이다.
형식이 생소하지만 위에를 뗴버리고 select문만 생각해도된다. 기존에 실제 DB에 있는 테이블을 사용했지만, CTE는 바로 위의 WITH절에서 정의한 'CTE_테이블이름'을 사용하는 것만 다르다. 즉 다른 형식의 테이블이 하나더 존재한다고 생각하면된다.

 

 

예시를 통해 이해해보자

usertbl

usertbl 이라는 테이블이 존재한다고 보자

회원테이블에서 지역별로 가장큰키와 그 사람들의 평균을 구한다고했을때, 2개의 select문이 나온다 하나는

가장 큰 키를 구하는것이고 다른하나는 나온결과로 평균을 구한다는 것이다.

-- 회원테이블에서 지역별로 가장큰 키
SELECT addr, MAX(height) FROM usertbl GROUP BY addr;

 

나온 쿼리문으로 with 구문으로 묶는다.

 

WITH with_table(addr, m_height)                              -- with의 새로운 table
AS
(SELECT addr, MAX(height) FROM usertbl GROUP BY addr)        -- 가장 큰키를 구하는 쿼리문
SELECT AVG(m_height) AS '각지역별 최고키의 평균' FROM with_table   -- 평균을 구하는 쿼라문

결과

 

여기서 with_table은  실존하는 테이블은 아니라 쿼리문의 결과이며,  가장 큰 키를 구하는 쿼리문이라고 생각하면 간단해진다. 이것이 CTE의 장점 중 하나이다. 구문을 단순화시킨다. 단 with_table () 가로안의 열과 select 열의 개수가 일치해야한다.

 

 

 

중복 CTE

CTE는 뷰와 그용도는 비슷하지만 구문이 끝나면 소멸된다.그리고 중복 CTE가 가능하다.

 

중복CTE 형식
WITH AAA(컬럼들)
AS (AAA의 쿼리문),
BBB(컬럼들)
AS(BBB 쿼리문),
CCC(컬럼들)
AS( CCC의 쿼리문)
SELECT * FROM [AAA 또는 BBB 또는 CCC]

이와같은 형식의 중복 CTE가 가능하다. 주의할 점은 CCC의 쿼리문에서는 AAA나 BBB를 참조할 수 있지만 AAA의 쿼리문이나 BBB에서는 CCC를 참조할 수 없다.