[SQL] UNION / UNION ALL, WITH

---

UNION / UNION ALL

 

UNION : 조회한 다수의 SELECT문을 하나로 합치고 싶을 때 유니온(UNION)을 사용할 수 있습니다.

UNION은 UNION (DISTINCT)와 UNION ALL 문 두 가지가 있습니다.

 

UNION (DISTINCT) : 중복되는 레코드를 제거 합니다. 

data1 data2 UNION data1 data2 >>> data1 data2

UNION ALL : 별도의 중복 제거 과정을 거치지 않고 결과를 내려줍니다.

data1 datat2 UNION ALL data1 data2 >> data1 data2 data1 data2

 

중복 여부의 판단은 SELECT된 튜플속에 속해있는 모든 컬럼의 값들 자체가 체크의 기준이 되는 것 입니다.

 

MYSQL이 내부적으로 UNION 과 UNION ALL을 처리하는 과정을 보면,

1. 최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary Table)을 메모리 테이블로 생성

2. UNION 또는 UNION DISTINCT인 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성

3. 서브쿼리 1 실행 후 결과를 Temporary 테이블에 복사

4. 서브쿼리 2 실행 후 결과를 Temporary 테이블에 복사

5. 만약 3, 4 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면 Temporary 테이블을 Disk Temporary 테이블로 변경 (이 때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)

6. Temporary 테이블을 읽어서 Client에 결과 전송

7. Temporary 테이블 삭제

이 때, UNION과 UNION ALL의 차이점은 2번 Temporary 테이블에서 인덱스를 생성하는지, 그렇지 않은지의 차이입니다.

 

실제 UNION 을 실행하는 데이터 건수에 따라서 다르겠지만, 1.5배 ~ 4배 가량 성능의 차이로 UNION ALL이 빠르게 처리됩니다.

 

결론적으로는,

1. UNION이든지 UNION ALL이든지 사실 둘다 좋은 SQL작성은 아니라는 것 입니다. UNION이 필요하다는 것은 사실 두 엔터티(테이블)가 하나의 엔터티(테이블)로 통합이 되었어야 할 엔터티들이었는데, 알 수 없는 이유로 분리 운영되는 경우가 상당히 많습니다.

즉, 모델링 차원에서 엔터티를 적절히 통합하여 UNION의 요건을 모두 제거해야 합니다.

2. 두 집합에 절대 중복된 튜플(레코드)가 발생할 수 없다는 보장이 있다면 UNION ALL을 꼭 사용해야 합니다. 두 집합에서 모두 각각의 PK를 조회하는데, 그 두 집합의 PK가 절대 중복되지 않는 형태

3. 중복이 있다 하더라도 그렇게 문제가 되지 않는다면 UNION 보다는 UNION ALL을 사용해야 합니다.

4. 만약 UNION 이나 UNION ALL 을 사용해야 한다면, 최소 필요 컬럼만 SELECT 합니다.

 

WITH

하나의 SQL문에서 가공한 테이블을 계속해서 사용해야 할 때가 있습니다.

가공한 테이블이 쿼리 안에서 계속 쓰다 보면 가독성이 떨어져 혼란스럽고 개발하기 복잡해지는 경우가 종종 있습니다.

 

이 떄, WITH구문을 사용하게 되면 서브쿼리로 추출된 데이터를 별칭을 지정해서 임시 테이블이나 VEIW와 같이 관리할 수 있습니다.

이를 통해 특정 서브쿼리의 결과를 관리할 수 있으며 같은 서브쿼리가 필요할 때 별칭을 주었던 임시 테이블에 대해 쿼리를 작성할 수 있습니다.

 

뷰가 쿼리 결과문을 테이블로 저장하는게 아니라, 그냥 별칭만 해 주는 것으로 뷰를 불러오면 쿼리문이 그대로 실행하는 것과 같습니다.

 with는 가상의 테이블, 즉 테이블이 있어 메모리를 차지한 다는 단점이 있습니다.

 

with절을 사용하는 이유이자 WITH절의 장점은 temp라는 임시 테이블을 사용해서 장시간 걸리는 쿼리를 저장해놓고 저장해놓은 데이터를 엑세스 하기 때문에 성능이 좋습니다.

그러나, 너무 남발하게 된다면 임시 테이블이 견딜 수 있는 정도가 넘어가서 다같이 느려집니다.

 

---

table 1 : 년도, 학생번호, 과목번호, 점수 

table 2 : 학생번호, 학생이름

table 3: 과목번호, 과목이름

 

2019년도에 대해 월에대한 과목별 점수에 대한 소계 및 월별소계 그리고 총 합계를 구하십시오.

 

라는 문제였는데, 

임시테이블과 UNION ALL을 사용하라는 조건이 있었습니다.

table1을 메인으로 잡고, table2와 3을 outer join으로 합친 임시테이블을 가지고 UNION ALL 을 통해 값을 계산하였습니다.

 

UNION ALL을 하면, 각각의 서브쿼리는 ORDER BY가 동작되지 않기 때문에,

SELECT를 한번 더 덮어주고 사용했습니다.

 

WITH TEMP_TABLE(YYYYMM, NO_EMP, NM_EMP, CD_SUBJECT , NM_SUBJECT, NUM_SCORE) AS (
SELECT T1.YYYYMM, T1.NO_EMP ,T2.NM_EMP, T1.CD_SUBJECT ,T3.NM_SUBJECT, T1.NUM_SCORE
FROM table1 AS T1
LEFT OUTER JOIN table2 AS T2 ON T1.NO_EMP =T2.NO_EMP 
LEFT OUTER JOIN table3 AS T3 ON T1.CD_SUBJECT =T3.CD_SUBJECT
WHERE YYYYMM BETWEEN '201901' AND '201912'
)
(
SELECT T4.YYYYMM, T4.NM_EMP, T4.NM_SUBJECT, T4.NUM_SCORE
FROM (
	SELECT YYYYMM, NO_EMP, NM_EMP ,CD_SUBJECT , NM_SUBJECT ,NUM_SCORE, '1' AS SORT1, '1' AS SORT2
	FROM TEMP_TABLE 
	UNION ALL
	(SELECT YYYYMM,'' AS NO_EMP, '과목소계' AS NM_EMP ,CD_SUBJECT, NM_SUBJECT , SUM(NUM_SCORE) AS NUM_SCORE, '2' AS SORT1, '1' AS SORT2
	FROM TEMP_TABLE
	GROUP BY YYYYMM, CD_SUBJECT)
	UNION ALL
	(SELECT YYYYMM,'' AS NO_EMP, '월별소계' AS NM_EMP,'999' AS CD_SUBJECT,'' AS NM_SUBJECT , SUM(NUM_SCORE) AS NUM_SCORE, '3' AS SORT1, '1' AS SORT2
	FROM TEMP_TABLE 
	GROUP BY YYYYMM)
	UNION ALL
	(SELECT '2019' AS YYYYMM, '' AS NO_EMP, '총소계' AS NM_EMP,'9999' AS CD_SUBJECT,'' AS NM_SUBJECT , SUM(NUM_SCORE) AS NUM_SCORE, '4' AS SORT1, '2' AS SORT2
	FROM TEMP_TABLE)
) T4 
ORDER BY SORT2, T4.YYYYMM, T4.CD_SUBJECT , SORT1, T4.NO_EMP);

 

임시 SORT를 위해 SORT1 과 2를 만들었고, 

ORDER BY 되는 과정을 아래 그림으로 그렸습니다.

결과 화면