[SQL] WITH절 사용하는 방법
SQL문을 작성하다 보면 같은 Query구문을 반복해서 작성하는 경우가 발생합니다. 이런 경우 보통 1~2번의 반복은 개발자 입장에서 작성하지만, 잦은 반복 또는 동일 구문에 대한 조건 등의 추가로 여러 번 수정을 하는 경우 귀찮음을 느끼기 마련입니다. 이런 경우 개발자가 생각하는 생각은 '아 매크로처럼 어디다 만들어두고 한 번에 관리되면 좋겠다.'라는 생각을 하게 됩니다.
모든 개발 언어들이 그렇듯, 개발자의 생각은 비슷한 부분이 많습니다. 때문에 이런 반복적인 사용을 가능하도록 제공하는 게 WITH 절입니다. 기본적으로 RDBMS계의 3 대장 (Oracle, MySQL, MS-SQL)에서 지원되는 부분이라 할 수 있습니다.
먼저 다음과 같이 두개의 테이블을 가정하겠습니다.
WITH절 이해하기
TB_SITE (판매사이트)
| SITE_CODE | SITE_NAME |
| 101 | 네이버쇼핑 |
| 102 | G마켓 |
| 103 | 11번가 |
TB_SITE_VENDOR (판매 사이트별 업체)
| SITE_CODE | VNDR_CODE | VNDR_NAME |
| 101 | 1001 | 애플 |
| 101 | 1002 | 삼성 |
| 102 | 1001 | 애플 |
| 102 | 1003 | 아이리버 |
두 테이블을 JOIN을 해서 네이버 쇼핑에 대해서 만 보고 싶은 경우 다음과 같은 SQL문을 작성할 수 있을 겁니다.
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE해당 Query문은 Inner Join으로 다음과 같은 결과가 나옵니다. Inner Join에 대한 글은 추후에 작성해 드리도록 하겠습니다.
| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME |
| 101 | 네이버쇼핑 | 1001 | 애플 |
| 101 | 네이버쇼핑 | 1002 | 삼성 |
| 102 | G마켓 | 1001 | 애플 |
| 102 | G마켓 | 1003 | 아이리버 |
이 중에서 '네이버 쇼핑'만을 추출해서 쓰고 싶을 때 다음과 같이 조건을 걸어 줄 수 있습니다.
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME |
| 101 | 네이버쇼핑 | 1001 | 애플 |
| 101 | 네이버쇼핑 | 1002 | 삼성 |
이렇게 작성한 완성된 하나의 테이블 이외에 'G마켓' 데이터만을 추출하는 SQL을 만들어서 서로 이어 붙이고 싶다면 어떻게 해야 할까요?
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME |
| 101 | 네이버쇼핑 | 1001 | 애플 |
| 101 | 네이버쇼핑 | 1002 | 삼성 |
| 102 | G마켓 | 1001 | 애플 |
| 102 | G마켓 | 1003 | 아이리버 |
다음과 같이 작성하면, '네이버 쇼핑'에 대한 데이터 뒤에 'G마켓'에 대한 데이터가 붙을 겁니다. 물론 조건만 조정하면 한 개의 SQL문으로도 가능하지만 지금은 WITH절의 사용에 대해 이야기하고 있기 때문에 일부러 UNION ALL을 사용하였습니다.
만약 여기에서 업체가 '애플'인 데이터만 추출하려면 어떻게 해야 할까요?
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
AND V.VNDR_CODE = '1001'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'
AND V.VNDR_CODE = '1001'
다음과 같이 각각의 SQL문에 AND V.VNDR_CODE = '1001'을 추가해주면 될 겁니다. 하지만 이전에도 말씀드린 것처럼 2개까지는 개발자에게 인내심 내에서 허용 범위이지만, 만약 저런 SQL문이 여러 개이면 각각의 SQL문에 작성하는 것은 매우 귀찮은 일일 겁니다. (물론 데이터의 정합성을 위해서도 올바르지 않은 방법이긴 합니다.)
이때 다음과 같이 작성을 하면 결과는 조금 더 다루기 쉬워집니다.
WITH VW_VENDOR AS (
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'
)
SELECT *
FROM VW_VENDOR
WHERE VNDR_CODE = '1001'
| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME |
| 101 | 네이버쇼핑 | 1001 | 애플 |
| 102 | G마켓 | 1001 | 애플 |
문법은 다음과 같습니다
/* 1개의 임시테이블 */
WITH 임시테이블명 AS (
SUB QUERY문 (SELECT절)
)
SELECT 컬럼, [컬럼, ...]
FROM 임시테이블명
/* 2개 이상의 임시테이블 */
WITH
임시테이블명1 AS (
SUB QUERY문 (SELECT절)
),
임시테이블명2 AS (
SUB QUERY문 (SELECT절)
)
SELECT 컬럼, [컬럼, ...]
FROM 임시테이블명1
, 임시테이블명2다음과 같이 작성하면 임시 테이블을 재사용이 가능하기 때문에 조건절의 조정이 수월해집니다. 이는 서브 쿼리의 다음과 같습니다.
SELECT V.*
FROM (SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'
) V
WHERE V.VNDR_CODE = '1001'차이점은, WITH로 사용한 임시 테이블은 해당 SQL 내에서 재사용이 가능하다는 점이기 때문에, 한 번만 사용하는 SQL문에서는 굳이 WITH 절을 사용할 필요가 없지만, 재사용을 필요한 상황에서는 WITH절을 사용하는 게 개발자 입장에선 편리합니다.
여기에서 제가 개발자 입장에 강조를 하는 이유는 WITH 절이나 Sub Query는 VIEW라는 임시 저장을 만드는 행위이기 때문에 SQL문 작성에 좋은 습관은 아닙니다. 오히려 위의 SQL문에서는 다음과 같이 작성하는 게 RDBMS 자원 사용 입장에서는 더 효율적이기 때문에 Query를 더욱더 고민해서 작성하는 게 올바른 방법이라고 할 수 있습니다.
SELECT S.SITE_CODE, S.SITE_NAME, SV.VNDR_CODE, SV.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE IN ('101', '102')
AND SV.VNDR_CODE = '1001'
SELECT V.*를 꼭 써야 할까?
별표(*)만 써도 웬만한 Database에서 무방하긴 합니다.
다만 실무에서는, 저렇게 Sub Query만으로 완성된 Query보다는 저 Sub Query에 추가로 JOIN 하여 추가 정보를 추출하는 경우가 많습니다. (물론 이건 업무마다 다를 수 있습니다.)
만약, 아래의 테이블에 존재한다고 가정하고
TB_VENDOR_USER (업체 직원 정보)
| VNDR_CODE | USER_NAME | POSITION | AGE |
| 1001 | 팀쿡 | CEO | 61 |
| 1001 | 애플 영업맨 | SALES | 35 |
| 1002 | 이재용 | CEO | 54 |
| 1002 | 삼성 영업맨 | SALES | 28 |
| 1002 | 삼성 영업맨2 | SALES | 37 |
USIER_NAME : 업체의 직원 이름
POSITION : 직책
AGE : 나이
만약, 위 기존 결과 + 대표자 명을 결과로 보고 싶을 때 단순 별표(*)만 해준 경우
SELECT *
FROM (SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'
) V
JOIN TB_VENDOR_USER VU ON V.VNDR_CODE = VU.VNDR_CODE AND VU.POSITION = 'CEO'
WHERE V.VNDR_CODE = '1001'| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME | VNDR_CODE | USER_NAME | POSITION | AGE |
| 101 | 네이버쇼핑 | 1001 | 애플 | 1001 | 팀쿡 | CEO | 61 |
| 101 | G마켓 | 1001 | 애플 | 1001 | 팀쿡 | CEO | 61 |
1. VNDR_CODE가 중복되어 표시됨
2. 대표 명 이외에 POSITION, AGE 등이 포함되어 표시됨
단순히 개인적으로 결과를 보는 경우에는 상관없지만, 다른 이에게 데이터 전달 시에는 이러한 정보들 정제를 해야 하는 경우가 있다 보니, 이미 SubQuery에서 정제된 테이블은 V.*로 전체를 보여주고 그 외에 추가 JOIN이 된 부분은 보여줄 Column만 지정하여 사용하곤 합니다.
SELECT V.*
, VU.USER_NAME
FROM (SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '101'
UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
FROM TB_SITE AS S
JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
WHERE S.SITE_CODE = '102'
) V
JOIN TB_VENDOR_USER VU ON V.VNDR_CODE = VU.VNDR_CODE AND VU.POSITION = 'CEO'
WHERE V.VNDR_CODE = '1001'| SITE_CODE | SITE_NAME | VNDR_CODE | VNDR_NAME | USER_NAME |
| 101 | 네이버쇼핑 | 1001 | 애플 | 팀쿡 |
| 101 | G마켓 | 1001 | 애플 | 팀쿡 |