UNION ALL 이해하기
조인은 데이터 집합을 좌우로 연결한다. 반면에 UNION ALL은 데이터 집합을 상하로 연결한다.
아래는 UNION ALL을 사용해 매장 건수와 회원 건수, 상품 건수를 보여주는 SQL이다. 조인과 다르게 데이터 집합을 연결하는 조건을 고려하지 않아도 된다. UNION ALL로 연결 할 데이터 집합의 컬럼 수만 잘 맞추면 된다.
-- [SQL-7-12-1]
SELECT 'Shop' DV, COUNT(*) Cnt
FROM startdb.Shop T1
UNION ALL
SELECT 'Member' DV, COUNT(*) Cnt
FROM startdb.Member T2
UNION ALL
SELECT 'Item' DV, COUNT(*) Cnt
FROM startdb.Item T3;
DV Cnt
------ ----
Shop 300
Member 9999
Item 21
SQL
복사
UNION ALL을 사용할 때는 다음의 내용을 주의해야 한다.
•
상하의 SELECT 절 컬럼 수가 같아야 한다.
•
상하로 같은 위치의 컬럼은 같은 자료형을 사용해야 한다.
◦
예를 들어 UNION ALL 위쪽 SELECT절 첫 번째 컬럼이 문자형이면,
▪
아래쪽의 첫 번째 컬럼도 문자형이어야 한다.
◦
상황에 따라서는 상하의 자료형이 같지 않아도 에러 없이 실행된다.
▪
자동형변환으로 자료형이 자동으로 맞추어 졌기 때문이다.
▪
상황에 따라 자동형변환이 안될 수도 있으므로 맞추어 작성하는 것이 좋다.
아래는 주문년월별 주문금액과 주문건수를 보여주는 SQL이다.
-- [SQL-7-12-2]
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,SUM(OrdAmt) OrdAmt
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m');
OrdYm OrdAmt OrdCnt
------ ----------- ------
202101 2590000.000 501
202102 3154500.000 610
202103 3858000.000 746
SQL
복사
위 SQL의 결과를 보면 년월별로 주문금액과 주문건수가 각각의 컬럼으로 출력되고 있다. 위와 같은 결과를 아래와 같이 변형해보려고 한다. 주문금액과 주문건수를 컬럼이 아닌 로우로 분리하려고 하는 것이다.
OrdYm VAL_Nm VAL
------ ------ -----------
202101 AMT 2590000.000
202102 AMT 3154500.000
202103 AMT 3858000.000
202101 CNT 501.000
202102 CNT 610.000
202103 CNT 746.000
SQL
복사
위와 같은 결과는 UNION ALL을 활용하면 쉽게 구할 수 있다. 년월별 주문금액 SQL과 년월별 주문건수 SQL을 각각 구해서 UNION ALL 처리하면 된다. 아래와 같다.
-- [SQL-7-12-3]
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,'AMT' VAL_Nm
,SUM(OrdAmt) VAL
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
UNION ALL
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,'CNT' VAL_Nm
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m');
SQL
복사
UNION ALL의 ORDER BY
방금 살펴본 SQL 결과의 출력 순서를 변경하고 싶다면 UNION ALL 마지막 블록 아래에 ORDER BY를 추가해야 한다.
-- [SQL-7-12-4]
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,'AMT' VAL_Nm
,SUM(OrdAmt) VAL
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
-- > 이곳에 ORDER BY를 추가할 수 없다.
UNION ALL
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,'CNT' VAL_Nm
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
ORDER BY OrdYm, VAL_Nm; -- > ORDER BY 추가
OrdYm VAL_Nm VAL
------ ------ -----------
202101 AMT 2590000.000
202101 CNT 501.000
202102 AMT 3154500.000
202102 CNT 610.000
202103 AMT 3858000.000
202103 CNT 746.000
SQL
복사
UNION ALL이 포함된 SQL에서 ORDER BY를 사용할 때는 다음 사항을 주의하기 바란다.
•
ORDER BY는 UNION ALL의 마지막 쿼리 블록 아래에만 작성할 수 있다.
◦
UNION ALL 중간 쿼리 블록에 ORDER BY를 사용할 수 없다.
•
ORDER BY는 UNION ALL이 모두 처리된 데이터 집합에 대해서 처리된다.
•
ORDER BY에는 다음 규칙으로만 정렬 기준을 줄 수 있다.
◦
UNION ALL 첫 번째 블록의 SELECT 절에서 정의한 컬럼의 별칭
◦
SELECT 절의 컬럼 위치 순번
•
ORDER BY의 컬럼으로 특정 쿼리 블록의 테이블의 컬럼을 지정할 수 없다.
◦
ORDER BY에 DATE_FORMAT(T1.OrdDtm,'%Y%m') 를 사용하면 에러가 발생합니다.
조인과 UNION ALL에 따른 데이터 집합의 결합 차이를 그림으로 비교해보면 다음과 같다.
•
조인은 데이터를 좌우로 연결하며, 관계차수와 조인조건 만족 여부에 따라 결과 건수가 늘어날 수도 있고 줄어들 수도 있다.
•
UNION ALL은 데이터 집합을 상하로 연결하며, 연결하는 데이터의 건수를 합한 만큼의 결과 건수가 만들어진다.
UNION
UNION ALL과 비슷한 기능의 UNION도 있다. UNION도 UNION ALL과 마찬가지로 두 데이터집합을 상하로 연결한다. 다만 UNION은 데이터 연결 후에 중복된 레코드를 제거하는 추가적인 기능이 동작한다.
UNION은 결합 데이터에 중복을 제거해야 하는 경우가 아니면 사용하지 않는 것이 좋다. 중복을 제거하기 위한 내부적인 부하가 추가로 발생하기 때문이다. UNION을 사용해야 하는 경우는 잘 없다. 중복 제거가 꼭 필요한 상황이 아니라면 UNION ALL을 쓰는 것이 맞다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-12-1
2020년부터 2023년까지의 주문에 대해 년도별 주문건수와 년도별 주문이 존재하는 회원수를 보여주세요. 주문건수와 주문이 존재하는 회원수를 각각의 레코드로 구분해서 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건
◦
OrdDtm이 2020년 1월 1일부터 2023년 12월 31일까지 데이터
•
조회 컬럼: OrdYY, 데이터구분, 값
•
추가 조건
◦
OrdYY: OrdDtm의 년도 값
◦
OrdYY별로 데이터를 집계합니다.
◦
데이터구분: ‘1. 주문건수’ 또는 ‘2. 회원수’라는 값을 가집니다.
▪
데이터 구분에 따라 두 개의 쿼리를 만들어 UNION ALL로 결합합니다.
◦
값
▪
데이터구분이 ‘1. 주문건수’인 경우는 주문의 건수를 표시합니다.
▪
데이터구분이 ‘2. 회원수’인 경우는 주문이 존재하는 회원수를 표시합니다.
•
COUNT(DISTINCT MemberId) 를 사용합니다.
•
정렬 조건: OrdYY로 먼저 정렬한후 데이터구분에 따라 정렬합니다.
OrdYY 데이터구분 값
----- ----------- ------
2020 1. 주문건수 9623
2020 2. 회원수 1716
2021 1. 주문건수 14443
2021 2. 회원수 2123
2022 1. 주문건수 41999
2022 2. 회원수 7415
2023 1. 주문건수 544906
2023 2. 회원수 6742
SQL
복사
Upper: 7. JOIN
