[mssql]환상의 CROSS JOIN

안녕하십니까? 유일환(ryu1hwan@hotmail.com)

이번에는 CROSS JOIN에 대해 적어볼까 합니다.

환상의 CROSS JOIN이라고 제목은 거창하지만.. 실제는 안 그럴 수도 있습니다.^^

CROSS JOIN은 두 개의 집합(테이블)을 아무 조건없이 연결시키는 조인을 말합니다.

수학적인 용어(?)로 카테션 곱이라고 하더군요.

CROSS JOIN은 아무런 조건없이 두 개의 테이블을 연결하는 조인입니다.

일반 조인은 조인 조건이 참인것만 연결하지만, CROSS JOIN은 두테이블의 데이터를 무조건 연결합니다.

아무런 조건없이 테이블을 연결하므로 조인된 결과는 두 집합의 건수의 곱만큼 만들어지게 됩니다.

(여기서 집합이란 테이블내에 데이터 중 WHERE등을 통해 걸러진 결과 집합을 뜻합니다.
실제 WHERE등의 조건을 통해 줄어들지 않은 많은 데이터를 CROSS JOIN을 한다면 엄청난 부하를 가져올 수
있습니다.)

만약에 A테이블에 100건, B테이블에 10건이 있을 경우 CROSS JOIN을 수행하게 되면,

결과는 1000건이 만들어 지게 됩니다.

그러므로 CROSS JOIN을 사용할 때는 주의가 필요하죠. 진정 CROSS JOIN이 필요한 곳에만

사용을 해야 겠죠.


1. 개발 초기에 많은 테스트 데이터들을 만들고 싶을 때

CROSS JOIN의 결과는 두 테이블의 곱만큼 발생이 된다고 했습니다.

개발 초기에는 실제 운영시 만큼의 데이터들이 발생되어 있지 않으므로

실제 운영시에 발생될 문제들을 쉽게 발견할 수 없습니다. 그러므로

테스트 환경에서도 운영시만큼 데이터들을 발생시켜서 테스트를 하는 작업이 필요합니다.

다음처럼 CROSS JOIN을 사용해서 많은 테스트 데이터들을 만들어 낼 수 있습니다.
USE Northwind

SELECT COUNT(*)
FROM Orders T1

SELECT * INTO Test_Orders
FROM Orders
WHERE 1 <> 1

--저장 프로시져를 사용해서 Orders테이블의 컬럼명들을 가져온다.
sp_columns orders
--여기서 identity성질을 가진 OrderID를 제외하고 모든 컬럼명을 카피해서 다음 SQL을 만든다.

INSERT INTO Test_Orders
(CustomerID, EmployeeID, OrderDate,
RequiredDate, ShippedDate, ShipVia,
Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode,
ShipCountry)
SELECT T1.CustomerID, T1.EmployeeID, T1.OrderDate,
T1.RequiredDate,T1.ShippedDate, T1.ShipVia,
T1.Freight, T1.ShipName, T1.ShipAddress,
T1.ShipCity, T1.ShipRegion, T1.ShipPostalCode,
T1.ShipCountry
FROM Orders T1
CROSS JOIN (SELECT '1' no
UNION ALL
SELECT '2' no
UNION ALL
SELECT '3' no
UNION ALL
SELECT '4' no
UNION ALL
SELECT '5' no) T2


SELECT COUNT(*)
FROM Test_Orders

결과를 확인해 보면, 830건이었던 Orders데이터를 4150건으로 만들어 낸 것을 알 수 있습니다.

이것의 CROSS JOIN의 활용이라 할 수 있죠. 데이터를 뻥튀기 시켜주는 역할을 잘 할 수 있는 놈이죠.

이와 같은 방법을 CASE문과 같이 사용한다면, 좀 더 현실에 가까운 테스트 데이터들도 만들어 낼 수가 있죠.



2. 가로로 펼쳐진 데이터들을 세로로 표현하고자 할 경우

이 방법 역시 CROSS JOIN을 사용하는 멋진 방법이라고 생각하고 있습니다.

현재 만들어진 ERD의 상태가 어떻느냐에 따라 이런 방법이 사용되어 지게 됩니다.

이번 경우는 실제 사례를 들어서 설명하겠습니다.

제가 프로그램을 개발하고 있는 테이블중에 서술형 결과라는 테이블이 있습니다.

환자의 상태를 검사하고 서술형으로 검사 결과를 입력하게 되는 테이블입니다.

테이블 구조는 다음과 같습니다.

CREATE TABLE TBL0005
( OrderKey numeric(18,0) primary key,
Msg1 varchar(500) default '',
Msg2 varchar(500) default '',
Msg3 varchar(500) default '',
Msg4 varchar(500) default '',
Msg5 varchar(500) default '',
Msg6 varchar(500) default '',
Msg7 varchar(500) default '',
Msg8 varchar(500) default '',
Msg9 varchar(500) default '',
Msg10 varchar(500) default ''
)


이 테이블에 다음과 같은 데이터들을 입력해 보도록 하죠.

INSERT INTO TBL0005 (OrderKey, Msg1, Msg2, Msg3)
VALUES (1,
'이 환자는 무슨무슨 이유로 인해',
'어쩌고 저쩌고 입니다.',
'그러므로 우찌우찌 해야 합니다.')

그리고, 서술형 결과가 아닌, 일반 수치나 문자값들이 저장되는 테이블이 있습니다.
CREATE TABLE TBL0203
( OrderKey numeric(18,0) primary key,
TestName char(10),
Result varchar(100)
)

INSERT INTO TBL0203(OrderKey, TestName, Result)
VALUES (1, 'A검사', '10')
INSERT INTO TBL0203(OrderKey, TestName, Result)
VALUES (2, 'B검사', '15')

위와 같은 서술형 결과와 문자형 데이터들을 하나의 쿼리를 통해 보여주고자 합니다.

서술형 결과 각각의 Msg1부터 Msg10까지는 문자형 결과의 Result와 각각 매치가 되는 것이죠.

두 테이블의 결과들을 하나의 결과로 만들어 내기 위해서 두 테이블을 UNION ALL로 연결해야 합니다.

SELECT TestName, Result
FROM TBL0203
UNION ALL
SELECT '서술형결과', Msg1
FROM TBL0005
UNION ALL
SELECT '서술형결과', Msg2
FROM TBL0005
UNION ALL
SELECT '서술형결과', Msg3
FROM TBL0005

이러한 방법이 있을 수 있죠.

하지만, 우리는 CROSS JOIN을 응용해서도 위와 같은 결과를 만들 수 있습니다.

CROSS JOIN을 보기 전에 위 테이블과 보여주려는 결과 형태의 문제점을 생각해 보면

한 오더의 서술형 결과의 데이터들은 가로로 저장되어 있는데 보여주려는 결과는

세로로 되어 있다는 것입니다. 만약에 서술형 결과 자체를 세로로 저장할 수 있도록

테이블이 구현되어 있다면, 이런 많은 양의 UNION ALL이나, CROSS JOIN이 필요 없겠죠.

자.. 먼저, CROSS JOIN을 하기 위해 서술형 결과 테이블의 메세지 갯수인 10만큼의 숫자가

들어가 있는 테이블이 필요합니다.

다음과 같은 TMP_NO 를 만들도록 하죠

CREATE TABLE TMP_NO
( no int primary key
)

INSERT INTO TMP_NO VALUES(1)
INSERT INTO TMP_NO VALUES(2)
INSERT INTO TMP_NO VALUES(3)
INSERT INTO TMP_NO VALUES(4)
INSERT INTO TMP_NO VALUES(5)
INSERT INTO TMP_NO VALUES(6)
INSERT INTO TMP_NO VALUES(7)
INSERT INTO TMP_NO VALUES(8)
INSERT INTO TMP_NO VALUES(9)
INSERT INTO TMP_NO VALUES(10)

위의 TMP_NO테이블과 서술형 결과 테이블을 먼저 CROSS JOIN을 해보도록 하죠.

SELECT *
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

위의 쿼리문을 실행하면, 같은 값들이 10개씩 출력된 결과를 볼 수 있습니다.

그러나 이 결과들이 각각, 다른 no를 가진 것을 알 수 있습니다.

우리는 이 no를 사용해서 각 로우마다 특정 결과만을 보여줄 수 있습니다.

no가 1일때는 msg1만, no가 2일때는, msg2, no가 3일때는 msg3만 보여주는 방식입니다.

당연히 CASE문을 사용해야 겠죠.

SELECT OrderKey,
Result
FROM TBL0203 T1
UNION ALL
SELECT OrderKey,
CASE T2.no WHEN 1 THEN Msg1
WHEN 2 THEN Msg2
WHEN 3 THEN Msg3
WHEN 4 THEN Msg4
WHEN 5 THEN Msg5
WHEN 6 THEN Msg6
WHEN 7 THEN Msg7
WHEN 8 THEN Msg8
WHEN 9 THEN Msg9
WHEN 10 THEN Msg10
END
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

UNION ALL의 위쪽의 쿼리는 수치형 결과에서 값을 가져오는 쿼리이고

아래쪽의 쿼리가 옆으로 펼쳐져 저장된 서술형 결과를 밑으로 떨어뜨려 보여주는 쿼리입니다.

서술형 결과가 어떻게 밑으로 떨여졌는지 확인 하기 위해 다음처럼 단계적으로 접근해보도록 하죠..

먼저 TBL0005와 TMP_NO간에 CROSS JOIN만을 수행해서 결과를 살펴봅시다.

SELECT *
FROM TBL0005 T1 CROSS JOIN TMP_NO T2

위의 쿼리를 실행해 보면 TBL0005의 한 건인 데이터가 10건이 만들어진 것을 알 수 있습니다.

만약에 TBL0005에 데이터가 2건 있었다면 각각 10건씩 총 20건의 데이터가 만들어졌을 겁니다.

10건의 데이터가 모두 동일한 값을 가지고 있지만, TMP_NO의 no컬럼만이 1부터 10까지 숫자를 가진 것을

알 수 있습니다. 만약에 TMP_NO에 1부터 20까지의 숫자가 저장되어 있다면

총 20건의 no만 다른 동일한 데이터가 만들어 졌겠죠.

여기서 TMP_NO를 10건으로 마친것은 TBL0005의 Msg가 1부터 10까지만 저장되어 있기 때문입니다.

이 결과의 특징은 모두 같은 값이고 no만 1부터 10까지 가지고 있다는 것입니다.

그럼, 이 결과 집합에 CASE문을 사용해서 no가 1일때는 Msg1을, no가 2일때는 Msg2를 이런식으로 보여준다면

우리가 원하던 대로 Msg가 1부터 10까지 밑으로 떨어진 것을 알 수 있습니다.

우리가 원하는 결과를 만들기 위해 마지막으로 가공을 한다면, Msg가 빈 값을 제거할 필요가 있겠죠..

저장도 안되어 있는 결과를 괜히 보여줘서 공간을 낭비할 필요는 없으니까요.

이것 역시, 간단합니다.

다음과 같죠.

SELECT OrderKey,
Result
FROM TBL0203 T1
UNION ALL
SELECT T.OrderKey,
T.Result
FROM (
SELECT OrderKey,
CASE T2.no WHEN 1 THEN Msg1
WHEN 2 THEN Msg2
WHEN 3 THEN Msg3
WHEN 4 THEN Msg4
WHEN 5 THEN Msg5
WHEN 6 THEN Msg6
WHEN 7 THEN Msg7
WHEN 8 THEN Msg8
WHEN 9 THEN Msg9
WHEN 10 THEN Msg10
END Result
FROM TBL0005 T1 CROSS JOIN TMP_NO T2
) T
WHERE T.Result <> ''

이와 같이 Msg가 비어 있는 값을 제거하기 위해 CROSS JOIN된 결과를 FROM절의 파생된 테이블로 적어주고

외부에서 Msg가(Result) ''이 아닌 값만 출력하는 것입니다.

이것이 CROSS JOIN을 이용해서 옆으로 펼쳐진 데이터를 밑으로 떨어뜨리는 방법입니다.

이렇듯 우리는, 숫자만 가지고 있는 임시 테이블과, 기교만 있으면 쉽게 이런 결과를 만들어 낼 수 있는 것이죠.



3. 발생되지 않은 데이터 만들어 내기

이번 역시 제가 예전에 참여했던 프로젝트 중에 사용했었던 방법입니다.

나중에 SM하시는 분들이.. 모..이런.. SQL이 있지란 생각에 지워버리지 않았을까 생각도 되지만..

그 당시에는 굉장히 많이 고민해서 만들었던 SQL문입니다.

해당 프로젝트에서는 매출이 발생되면 데이터가 발생됩니다.

간략하게 다음과 같은 테이블에 데이터가 발생된다고 할 수 있죠.

DROP TABLE TBL_SALE

CREATE TABLE TBL_SALE
( SaleDate CHAR(8), --판매일자
ProductID INT, --제품ID
TradeType CHAR(2), --거래형태
Quantity INT, --수량
CONSTRAINT pk_TBL_SALE PRIMARY KEY(SaleDate, ProductID, TradeType)
)

딱 보시면, 이 테이블에 어떤 데이터들이 들어올지 쉽게 알 수 있을 겁니다.

단지, 약간 생소한 것이라면, 거래형태라는 것이 있습니다.

어떤 거래 형태로 데이터가 발생되었는지를 저장하는 컬럼이죠.

거래형태에는 현금, 카드, 외상, 등등이 있습니다.

지금 테스트를 위해서는 현금(01), 카드(02), 외상(03), 기타(04), 이 4가지가 있다고 생각을 해보도록 하죠.

위의 테이블에 테스트를 위해 임시 데이털르 입력해 보도록 합시다.

INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040301', 1, '01', 3)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040301', 1, '03', 4)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '01', 2)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '03', 4)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '02', 1)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040302', 1, '04', 2)
INSERT INTO TBL_SALE (SaleDate, ProductID, TradeType, Quantity)
VALUES ('20040303', 1, '04', 5)

위의 데이터를 조회해보면 다음과 같죠.
SELECT *
FROM TBL_SALE

SaleDate ProductID   TradeType Quantity   
-------- ----------- --------- -----------
20040301 1           01        3
20040301 1           03        4

20040302 1           01        2
20040302 1           02        1
20040302 1           03        4
20040302 1           04        2

20040303 1           04        2

데이터를 살펴보면 3월 1일에 1번 제품이 거래형태 1번과 3번으로 발생되었습니다.

3월 2일에는 1번부터 4번까지 모든 거래형태의 데이터가 발생되었고요, 3월 3일에는 4번 거래형태만 발생되었습니다.

사용자가 위 데이터를 조회하는데 발생되지 않은 거래형태의 제품에 대해서는 0의 수량을 갖도록 보기를 원합니다.

이런 결과를 원하는 것이죠.
SaleDate ProductID   TradeType Quantity   
-------- ----------- --------- -----------
20040301 1           01        3
20040301 1           02      0 --> 실제 존재하지 않는 데이터
20040301 1           03        4
20040301 1           02      0 --> 실제 존재하지 않는 데이터

20040302 1           01        2
20040302 1           02        1
20040302 1           03        4
20040302 1           04        2

20040303 1           01      0 --> 실제 존재하지 않는 데이터
20040303 1           02        0 --> 실제 존재하지 않는 데이터
20040303 1          03      0 --> 실제 존재하지 않는 데이터
20040303 1           04      2

이런 데이터를 표현하기 위해서 클라이언트에서 로직으로 처리하는 순간, 귀찮은 코딩들이 발생되어질 것입니다.

예외사항 잡아내고, 건수 계산하고, 모.. 이런 식의 코딩들이 들어가게 되죠.

하지만, CROSS JOIN을 사용해서 저런 데이터를 미리 만들어 낸다면 클라이언트에서는 단순히 결과를 보여주기만 하
면

되는 것이죠.

제가 생각해낸 방법의 첫단계는 이렇습니다.

SELECT *
FROM TBL_SALE T1
CROSS JOIN (SELECT '01' tradeType
UNION ALL
SELECT '02' tradeType
UNION ALL
SELECT '03' tradeType
UNION ALL
SELECT '04' tradeType) T2
WHERE T1.ProductID = 1
T2에는 UNION ALL을 이용해서 모든 거래형태를 미리 만들어 놓고, TBL_SALE과 CROSS JOIN을 해버립니다.

이렇게 하면, T1의 각 데이터마다 4개의 거래 형태가 만들어지게 됩니다.

3월 1일의 데이터가 다음과 같이 나타나게 됩니다.
SaleDate ProductID   TradeType Quantity    tradeType
-------- ----------- --------- ----------- ---------
20040301 1           01        3           01
20040301 1           01        3           02
20040301 1           01        3           03
20040301 1           01        3           04
20040301 1           03        4           01
20040301 1           03        4           02
20040301 1           03        4           03
20040301 1           03        4           04
TBL_SALE의 각 데이터가 4건씩 발생된 것을 알 수 있습니다.

이 데이터를 TBL_SALE의 SaldeDate와 ProductID와 T2쪽의 tradeType을 기준으로 GROUP BY를 수행합니다.

그 다음 Quantity를 SUM을 하는 겁니다.

SUM을 할 때는 CASE를 사용해서, TBL_SALE과 T2쪽의 tradeTtype 동일한 경우만 Quantity로, 아닌 경우는 0으로
SUM을 구합니다.

이 GROUP BY와 CASE를 적용하면 원하는 결과를 얻어 낼수 있게 됩니다.

다음과 같습니다.

SELECT T1.SaleDate, T1.ProductID, T2.tradeType,
SUM(CASE WHEN T1.tradeType = T2.tradeType THEN Quantity ELSE 0 END) Quantity
FROM TBL_SALE T1
CROSS JOIN (SELECT '01' tradeType
UNION ALL
SELECT '02' tradeType
UNION ALL
SELECT '03' tradeType
UNION ALL
SELECT '04' tradeType) T2
WHERE T1.ProductID = 1
GROUP BY T1.SaleDate, T1.ProductID, T2.tradeType
ORDER BY T1.SaleDate, T1.ProductID, T2.tradeType

위 SQL을 실행하면.. 짠!!!하고. 발생되지 않은 데이터도 0으로 표시할 수 있는 것을 알 수 있습니다.

^^ 여기서는 쿼리가 간단했지만, 이 쿼리를 생각하고 응용한다면, 꾀나 유용하게 사용할 수 있습니다.

하지만, 조심할 것은 실제 데이터가 많은 양에 대해 CROSS JOIN을 수행한다면 엄청난 부하를 가져올 수도 있습니다.

그러므로 결과를 먼저 충분히 줄여놓은 다음에, CROSS JOIN을 해야 합니다.

[출처] [mssql]환상의 CROSS JOIN|작성자 필립박