비재귀적 CTE(Common Table Expression): WITH

 CTE란 뷰, 파생 테이블 등과 같은 형식으로 일시적으로 뷰와같은 기능을 대체할 수 있는 기능이다. 복잡하게 보일 수 있는 쿼리문을 단순화 해준다는 장점이 있으며, WITH절과 함께 쓰인다. 실습을 통해 활용도를 알아보자.

 

-- 비재귀적 CTE
/*
<기본구조>
WITH CTE_테이블이름(열 이름)
AS
(
	<쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름 ;
*/

-- 간단하게 이해하기 위해서 buytbl의 총구매액을 구하는 실습을 다시 가져와보자

USE sqlDB ;
SELECT userid AS '사용자', SUM(price*amount) AS '총구매액'
	FROM buyTBL GROUP BY userid ;
/* 위의 테이블에서 총구매액을 내림차순으로 정렬하려면 쿼리문이 복잡해진다
이런 경우에서 CTE 테이블이 쿼리문을 단순화 한다는 점에서 유용하다.
위의 쿼리문이 abc 라는 테이블이라고 가정한다면, SELECT * FROM abc ORDER BY 총구매액 DESC ;으로 간단하게 표현이 가능하기 때문이다.
*/

WITH abc(userid, total)
AS
(SELECT userid, SUM(price*amount)
	FROM buyTBL GROUP BY userid )
SELECT * FROM abc ORDER BY total DESC ;

-- 다른 예시로 회원 테이블에서 각 지역별로 가장 큰 키를 한 명씩 뽑고 평균을 내는 연습을 해보자
WITH cte_userTBL(addr, maxHeight)
AS
(SELECT addr, MAX(height) FROM usertbl GROUP BY addr)
SELECT AVG(maxheight*1.0) AS '각 지역별 최고키의 평균' FROM cte_userTBL ;

/*
CTE와 뷰는 용도는 비슷하지만 뷰는 계속해서 다른 구문에서도 사용이 가능한 반면
CTE와 파생 테이블은 해당 구문을 실행 후에 소멸된다
*/

 위의 예시를 빌려 말하자면, buytbl에서 총구매액을 구하는 3줄 가량의 쿼리문에 추가적으로 데이터 정렬조건을 붙이거나 다른 결과를 추출하기 위해서 이후에 덧붙이면 쿼리가 더욱 복잡해질 것이다. 따라서 가장 기본적인 쿼리를 CTE로 묶고 그 CTE를 언급해 *일시적으로* 간소화하여 사용할 수 있는 것이다. 따라서, 존재하지 않는 테이블(CTE)을 생성해 더 복잡한 쿼리를 수행하기 위해서 언급하는 용도로 사용한다는 것이다.

 

 CTE는 중복으로 여러 CTE를 생성할 수도 있다.

WITH
AAA (열 이름들)
AS ( AAA 쿼리 ),
	BBB (열 이름들)
    	AS ( BBB 쿼리),
    CCC (열 이름들)
    	AS ( CCC 쿼리 )
SELECT * FROM [CTE 지목] ;

 이렇게 CTE 지정 속에 중복의 CTE를 만들 수 있다. 여기서 유의할 점은 CCC는 AAA와 BBB를 참조해 쿼리문을 만들 수 있지만 AAA나 BBB는 CCC를 참조할 수 없다. 즉, 순서대로 참조하여 쿼리문을 작성할 수 있지만 역행은 불가능하다.