[MSSQL]EXCEPT를 이용한 차집합 구하기(MSSQL 차집합)

서론

 

클라이언트에서 전달받은 데이터를 특정 테이블에 CREATE 또는 UPDATE 해야 하는 로직이 있었습니다. 클라이언트에서 전달받은 정보만으론 데이터를 CREATE 해야 하는지 UPDATE 해야 하는지 알 수 없는 상황이었습니다. 그래서 전달받은 데이터 집합과 특정 테이블의 데이터 집합의 차집합을 구하여 특정 테이블에 없는 데이터는 CREATE 하고 없는 데이터는 UPDATE 하는 로직을 생각하여 구현했습니다. 그 과정 속에서 알게 된 EXCEPT으로 차집합 구하는 방법을 예제를 통해 알아보겠습니다.

 

---

 

본론

 

[구문 형식]

 

SELECT [컬럼1], [컬럼2], ... [컬럼N]
FROM [테이블1]
EXCEPT
SELECT [컬럼1], [컬럼2], ... [컬럼N]
FROM [테이블2]

 

 

[특징]

 

1. 첫 번째 조회 집합(EXCEPT 왼쪽)에서 두 번째 조회 집합(EXCEPT 오른쪽)을 뺀 차집합을 구할 수 있습니다.

2. 양쪽 집합의 SELECT 컬럼 개수가 동일해야 합니다.

 

[예제]

 

--품목(ITEM)의 수량(QTY), 가격(PRICE) 정보가 있는 #TEMP_ITEM_TABLE테이블
CREATE TABLE #TEMP_ITEM_TABLE(
     ITEM NVARCHAR(10) PRIMARY KEY 
    ,QTY NUMERIC(10,0)
    ,PRICE NUMERIC(10,0)
)

INSERT INTO #TEMP_ITEM_TABLE
SELECT '제품1' AS ITEM, 100 AS QTY, 1000 AS PRICE
UNION ALL
SELECT '제품2' AS ITEM, 200 AS QTY, 2000 AS PRICE

--이전 #TEMP_ITEM_TABLE테이블 데이터 확인
SELECT * FROM #TEMP_ITEM_TABLE

--#TEMP_ITEM_TABLE에 INSERT 또는 UPDATE할 정보를 담고있는 #RECEIVE_TEMP_DATA 생성
SELECT '제품1' AS ITEM, 1000 AS QTY, 10000 AS PRICE--UPDATE DATA
INTO #RECEIVE_TEMP_DATA
UNION ALL
SELECT '제품2' AS ITEM, 2000 AS QTY, 20000 AS PRICE--UPDATE DATA
UNION ALL
SELECT '제품3' AS ITEM, 300 AS QTY, 3000 AS PRICE--INSERT DATA
UNION ALL
SELECT '제품4' AS ITEM, 400 AS QTY, 4000 AS PRICE--INSERT DATA
UNION ALL
SELECT '제품5' AS ITEM, 500 AS QTY, 5000 AS PRICE--INSERT DATA

--UPDATE
--#TEMP_ITEM_TABLE에 있던 기존 데이터는 INNER JOIN을 통해 UPDATE
UPDATE A
SET  A.QTY = B.QTY
	,A.PRICE = B.PRICE	
FROM #TEMP_ITEM_TABLE A
     INNER JOIN #RECEIVE_TEMP_DATA B ON A.ITEM = B.ITEM

--EXCEPT
--EXCEPT를 이용하여 #RECEIVE_TEMP_DATA와 #TEMP_ITEM_TABLE의 ITEM 차집합 구하기
SELECT A.ITEM
INTO #TEMP_INSERT
FROM #RECEIVE_TEMP_DATA A
EXCEPT
SELECT B.ITEM FROM #TEMP_ITEM_TABLE B

--INSERT
--#TEMP_ITEM_TABLE에 없는 데이터인 차집합 #TEMP_INSERT는 INSERT
INSERT INTO #TEMP_ITEM_TABLE
SELECT
	A.ITEM, A.QTY, A.PRICE
FROM
	#RECEIVE_TEMP_DATA A
	INNER JOIN #TEMP_INSERT B ON A.ITEM = B.ITEM
    
--이후 #TEMP_ITEM_TABLE테이블 데이터 확인
SELECT * FROM #TEMP_ITEM_TABLE

 

 

 

---

 

결론

 

EXCEPT 이외에도 NOT IN, OUTER JOIN, OUTER APPLY 등을 이용하여 차집합을 구하는 여러 방법들이 있습니다. 각 구문에 따라 옵티마이저가 실행 계획을 다르게 세우므로 상황에 따라 적절한 구문을 사용하여 차집합을 구현하시면 될 것 같습니다!