[SQL] WITH 문

업무 중 마주한 내용에 대해 추가적으로 알아보고자 공부하고 작성하였습니다.

WITH 문

 

쿼리를 작성하다 보면 서브쿼리 (subquery)를 작성하게 되는 경우가 많습니다.
이러한 서브쿼리를 반복해서 사용하게 되는 경우, 쿼리가 매우 복잡해지고 가독성도 좋지 않게 됩니다.
WITH 문은 이러한 문제를 해결하기 위한 방법입니다. '이름을 붙인 서브쿼리'라고 요약할 수 있으며, Oracle, MSSQL, MySQL, PostgreSQL 등 주요 RDBMS는 모두 지원합니다.
(MySQL의 경우 8.0버전부터 WITH문을 지원합니다.)

기본 구조는 다음과 같습니다.

 

WITH (테이블명)
AS (SELECT ~ FROM ~)
SELECT ~

 

다음과 같은 테이블이 있다고 가정하겠습니다.

TB_CARR

CARR_CODE	CARR_NAME
0000	SK Telecom
0001	KT
0002	LG U+
0003	알뜰폰

TB_MANF

CARR_CODE	MANF_CODE	MANF_NAME
0001	1000	Samsung Electronics
0000	1001	LG Electronics
0002	1001	LG Electronics
0000	1002	Apple
0002	1003	BlackBerry
0001	1004	SONY
0002	1002	Apple
0002	1000	Samsung Electronics

두 테이블을 JOIN 하는 쿼리 및 결과는 아래와 같습니다.

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE

 

CARR_CODE	CARR_NAME	MANF_CODE	MANF_NAME
0001	KT	1000	Samsung
0000	SK Telecom	1001	LG Electronics
0002	LG U+	1001	LG Electronics
0000	SK Telecom	1002	Apple
0002	LG U+	1003	BlackBerry
0001	KT	1004	SONY
0002	LG U+	1002	Apple
0002	LG U+	1000	Samsung Electronics

이 중에서 통신사가 SK Telecom인 경우만 보고 싶다면?

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0000'

 

이 중에서 통신사가 KT인 경우만 보고 싶다면?

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0001'

 

위 둘을 이어 붙이는 쿼리는 이렇게 작성할 수 있습니다.

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0000'
UNION ALL
SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0001'

 

그리고 여기서 제조사가 Samsung Electronics인 경우만 보고 싶다면?

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0000' AND B.MANF_CODE = '1000'
UNION ALL
SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE = '0001' AND B.MANF_CODE = '1000'

 

위의 각 항목에 (AND B.MANF_CODE = '1000') 을 추가해 주어야 합니다.
쿼리 작성에도 비효율적이며, 실수하기 쉽고, integrity (정합성) 유지에도 좋지 않습니다.

이럴 때 WITH 문을 사용하면 효율적으로 쿼리를 작성할 수 있습니다.

 

WITH PHONE_LIST AS (
    SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
    FROM TB_CARR AS A
    JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
    WHERE A.CARR_CODE = '0000'
    UNION ALL
    SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
    FROM TB_CARR AS A
    JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
    WHERE A.CARR_CODE = '0001'
)
SELECT * FROM PHONE_LIST
WHERE MANF_CODE = '1000'

 

위의 경우, 'PHONE_LIST'라는 이름을 붙인 임시 테이블을 생성한 셈이 됩니다.
즉, 필요한 테이블을 임시로 하나 생성해 두고, 필요할 때마다 호출해서 원하는 값을 조회할 수 있는 것입니다.
WITH 문을 사용하면 쿼리를 작성할 때, 유지/보수할 때 편리하고 가독성 있는 쿼리를 작성할 수 있습니다.
다만 DB의 효율성을 생각해 보면 약간의 손해를 감수하고 들어가야 합니다. WITH문, subquery 모두 임시 테이블을 생성하는 행위 (VIEW를 생성한다고 합니다)이기 때문에, DB의 자원을 추가로 소모하기 때문입니다.

위의 쿼리를 가장 효율적으로 짠다면 아래와 같이 작성할 수 있습니다.

 

SELECT A.CARR_CODE, A.CARR_NAME, B.MANF_CODE, B.MANF_NAME
FROM TB_CARR AS A
JOIN TB_MANF AS B ON A.CARR_CODE = B.CARR_CODE
WHERE A.CARR_CODE IN ('0000', '0001') AND B.MANF_CODE = '1000'

 

따라서 가장 좋은 것은 VIEW를 생성하지 않는 것이고, 생성해야 한다면, 혹은 가독성을 고려한다면 WITH문을 사용하면 될 것입니다.