[SQL] WITH절 사용하는 방법

---

이 버전에서는 TOC를 지원하지 않습니다. (ex. 모바일)

---

---

 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	애플	팀쿡