[DBMS] SELECT 문 - WITH절과 CTE

Nov 26, 2024

Contents

💡

WITH절은 CTE(Common Table Expression)를 표현하는 구문이다. CTE는 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다. CTE는 비재귀적(Non-Recursive) CTE와 재귀적(Recursive) CTE 두 갖가 있다.

1. 비재귀적 CTE

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


WITH CTE_테이블명 (컬럼명)

AS
(
		<쿼리문>
)

SELECT 컬럼명 FROM CTE_테이블명 ;

SELECT 절에 사용되는 ‘CTE_테이블명’ 테이블은 WITH 절에서 정의된 테이블이다.

즉 WITH 문으로 임시로 사용할 테이블을 만든 것이다.

아래의 쿼리는 buyTb1 에서 총 구매액을 구한다.


SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTb1 GROUP BY userID

이 결과에서 총 구매액이 많은 사용자 순서로 정렬하고 싶다면 어떻게 해야 할까? ORDER BY문을 사용하면 된다.


SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTb1 GROUP BY userID
ORDER BY [총구매액] DESC

이렇게 사용할 수 있다. 하지만 이런 경우 쿼리가 복잡해질 수 있다. 이때 WITH문을 사용해 쿼리를 단순화할 수 있다.

WITH문은 결과를 하나의 테이블로 생각하고 만든다.


WITH abc (userID, total)
AS

(SELECT userID, SUM(price*amount) FROM buyTb1 GROUP BY userID)

SELECT * FROM abc ORDER BY total desc

이렇게 결과 테이블을 하나의 테이블로 생각해 WITH절로 임시 테이블을 만든 후 그 테이블로 SELECT 에 사용한다.

다른 예로 연습해보자.

회원 테이블에서 각 지역별로 가장 큰 키를 가진 사람을 1명씩 뽑은 후에, 그 사람들 키의 평균을 내보자. 만약 전체의 평균이라면 집계 함수 AVG() 를 사용하면 되지만, 각 지역별로 가장 큰 키 1명을 우선 뽑아야 하므로 복잡해진다.

이럴 때 CTE 를 사용할 수 있다. 순차적으로 쿼리를 만들어보자.

1단계) 우선 각 지역별로 가장 큰 키를 조회한다.


SELECT addr, MAX(height) FROM userTb1 GROUP BY addr

2단계) 1단계의 쿼리를 WITH 절로 묶는다.


WITH CTE_userTb1 (addr,max_height)

AS
	(SELECT addr, MAX(height) FROM userTb1 GROUP BY addr)

3단계) 키의 평균을 구하는 쿼리를 구한다.


SELECT AVG(max_height*1.0) FROM CTE_userTb1

추가로 CTE는 다음과 같이 중복 CTE가 허용된다.


WITH 
	AAA (userID,total)
		AS
		(SELECT userID, SUM(price*amount) FROM buyTb1 GROUP BY userID),
	BBB (sumtotal)
		AS
		(SELECT SUM(total) FROM AAA),
	CCC (sumavg)
		AS
		(SELECT sumtotal/(SELECT count(*) FROM buyTb1) FROM BBB)
		
	SELECT * FROM CCC ;

💡

CTE에서 주의할 점은 CCC의 쿼리에서는 AAA나 BBB를 참조할 수 있지만, AAA의 쿼리에는 BBB나 CCC의 쿼리문을 참조할 수 없다.

2. 재귀적 CTE

💡

재귀적이라는 의미는 자기 자신을 반복적으로 호출한다는 의미이다. 주로 자기 자신을 포함한 하위 구조를 나타낼 때 사용한다.

재귀적 CTE의 기본 형식은 다음과 같다.


WITH CTE_테이블명 (컬럼명)

AS
	(
		<쿼리문 1 : SELECT * FROM 테이블1>
		
		UNION ASLL
		
		<쿼리문 2: SELECT * FROM 테이블2>
	)

SELECT * FROM CTE_테이블명 ;

여기서 첫번째 쿼리는 앵커 멤버(Anchor Member) , 두 번쨰 쿼리는 재귀 멤버(Recursive Member) 라고 지칭한다.

재귀적 CTE가 실행되는 순서는 다음과 같다.

<쿼리문 1> 을 실행한다. 이것이 루틴의 최초 호출에 해당한다. 그리고 기본값은 0으로 초기화된다.

<쿼리문 2> 을 실행한다. 기본값을 기본값+1 로 증가한다. SELECT의 결과가 빈 것이 아니라면, ‘CTE_테이블명’ 을 다시 재귀적으로 호출한다.

계속 2번을 반복한다. 단 SELECT의 결과가 아무 것도 없다면 재귀적인 호출이 중단된다.

외부의 SELECT문을 실행해서 앞 단계의 누적된 결과(UNION ALL) 을 가져온다.

재귀적 CTE를 만들기 위해 간단한 상사와 부하직원의 관계를 만들어보자.

직원이름 (emp)	상관 이름(manager)	부서(department)
나사장	null	null
김재무	나사장	재무부
김부장	김재무	재무부
이부장	김재무	재무부
우대리	이부장	재무부
지사원	이부장	재무부
이영업	나사장	영업부
한과장	이영업	영업부
최정보	나사장	정보부
윤차장	최정보	정보부
이주임	윤차장	정보부


USE sqlDB 

CREATE TABLE empTb1 (
	emp	NCHAR(3),
	manager NCHAR(3),
	department NCHAR(3)

)

INSERT INTO empTb1 VALUES ('나사장',null,null)
INSERT INTO empTb1 VALUES ('김재무','나사장','재무부')
INSERT INTO empTb1 VALUES ('김부장','김재무','재무부')
INSERT INTO empTb1 VALUES ('이부장','김재무','재무부')
INSERT INTO empTb1 VALUES ('우대리','이부장','재무부')
INSERT INTO empTb1 VALUES ('지사원','이부장','재무부')

INSERT INTO empTb1 VALUES ('이영업','나사장','영업부')
INSERT INTO empTb1 VALUES ('한과장','이영업','영업부')

INSERT INTO empTb1 VALUES ('최정보','나사장','정보부')
INSERT INTO empTb1 VALUES ('윤차장','최정보','정보부')
INSERT INTO empTb1 VALUES ('이주임','윤차장','정보부')

테이블을 생성 후 데이터를 입력한다.

이 테이블로 재귀적 CTE를 만들어보자.


WITH CTE_emp (empName, mgrName,dept,level)
AS

	(SELECT emp ,manager,department,0
	 FROM empTb1
	 WHERE manager IS NULL  -- manager 가 null인 값, 즉 사장을 조회
	
	 UNION ALL -- 쿼리문 1 과 쿼리문 2를 합치다. 

	 SELECT aa.emp, aa.manager, aa.department,bb.level +1
	 FROM empTb1 aa  JOIN CTE_emp bb -- 쿼리문 1에서 조회된 결과를 이용해 직속 상사를 찾는다.
	 ON aa.manager = bb.empName	
	)

SELECT * FROM CTE_emp ORDER BY dept,level

재귀적 CTE를 사용해 회사의 조직도를 알 수 있다.


WITH CTE_emp (empName, mgrName,dept,level)
AS

	(SELECT emp ,manager,department,0
	 FROM empTb1
	 WHERE manager IS NULL
	
	 UNION ALL

	 SELECT aa.emp, aa.manager, aa.department,bb.level +1
	 FROM empTb1 aa  JOIN CTE_emp bb
	 ON aa.manager = bb.empName	
	)

SELECT replicate('└',level) + empName as "직원이름", dept as "부서"
FROM CTE_emp ORDER BY dept ,level

replicate 함수는 값을 반복해서 반환한다. replicate('└',level) 는 ‘└' 이 값을 level 의 숫자 만큼 반환한다는 의미이다.