[MSSQL] MERGE 문법 정리

저번 포스팅에 이어 MERGE 문법에 대해 알아보자.

 

MERGE 문법은 하나의 쿼리문으로 데이터 소스와 대상 테이블을 비교해서 데이터를 동기화할 수 있는 기능을 제공하며, 단일 문에서 여러 DML 작업을 수행할 수 있다. 또한 MERGE문은 반드시 세미콜론(;)으로 종료해야 한다.

 

문법

사용 방법은 아래와 같이 정리해 보았다. 여기서 대상 테이블과 기준 테이블에 inline view가 들어가는 경우엔 inline view에 대한 aliasing이 반드시 필요하다.

MERGE 대상(target) 테이블 AS T
USING 소스(source) 테이블 AS A
ON 일치 조건 
WHEN MATCHED [AND 다른 조건 추가 가능]
THEN 수행할 쿼리 (UPDATE or DELETE)
WHEN NOT MATCHED [AND 다른 조건 추가 가능]
THEN 수행할 쿼리 (INSERT)
WHEN NOT MATCHED BY SOURCE [AND 다른 조건 추가 가능]
THEN 수행할 쿼리 (UPDATE or DELETE)
...
;

문법에 대해 설명하기 전에 위의 그림으로 MERGE의 세 가지 조건에 대해 알고 넘어가면 이해가 쉽다.

Target 테이블에 Source 테이블의 내용을 merge하려고 하는데 id를 기준으로 일치하는 행과 일치하지 않는 행을 구분할 수 있다.

 

• Target 테이블과 Source 테이블 모두 존재하는 경우엔 MATCHED 조건에 해당하고, 해당 행은 Target 테이블의 행을 Update하거나 Delete 할 수 있다.
• 마찬가지로 Target 테이블에는 존재하지만 Source 테이블에는 존재하지 않는 경우는 NOT MATCHED BY SOURCE 조건에 해당하며, 해당 행은 MATCHED와 마찬가지로 Target 테이블의 행을 Update하거나 Delete 할 수 있다.
• 마지막으로 Source 테이블에는 존재하지만 Target 테이블에는 존재하지 않는 경우는 NOT MATCHED BY TARGET 조건에 해당하고, 해당 행은 Target 테이블로 INSERT만 가능하다.

 

ON

대상 테이블과 소스 테이블을 비교(조인)할 조건을 지정한다. ex) id

ON 절에 추가 검색 조건을 넣어 대상 테이블의 행을 필터링하여 쿼리 성능을 향상하려고 하면 예기치 않은 잘못된 결과가 반환될 수 있다. (이는 뒤에서 추가로 설명할 것이다.)

 

WHEN

MATCHED

• MERGE문에 1개만 포함될 수 있다.

• MATCHED절을 2 개 포함시킬 경우, 추가 조건(AND condition)절을 지정해야 하고 각각 UPDATE / DELETE 동작을 지정해야 한다.

NOT MATCHED [BY TARGET]

• MERGE문에 1개만 포함될 수 있다.

• BY TARGET은 생략할 수 있다.

• ON절에 일치하지 않지만 추가 검색 조건(있을 경우)을 충족하는 모든 행에 대해 INSERT 수행한다.

NOT MATCHED BY SOURCE

• MERGE문에 최대 2개 올 수 있다.

• WHEN MATCHED가 적용되지 않은 경우에만 적용된다.

• 2개 포함시킬 경우, 추가 조건절을 지정해야 하고 각각 UPDATE / DELETE 동작을 지정해야 한다.

• 추가 검색 조건은 대상 테이블의 열만 참조할 수 있다.

MERGE는 동일한 행을 여러 번 Update하거나 Delete할 수 없다.

 

참고

• MERGE는 무조건 대상 테이블과 소스 테이블을 Full Scan 한다. Full Scan으로 한 행씩 조건을 검사하며 쿼리를 수행하기 때문이다.

• 만약, 3만 개의 행이 있는 테이블 중에 '특정 컬럼 = 특정 값'인 행이 100개가 있는데 그 100개의 행만을 대상으로 MERGE를 하고 싶다면?

  1. ON절에 '특정 컬럼 = 특정 값'을 넣어도 Full Scan을 한다. 또한 원하는 결과가 나오지 않을 수도 있다.

  2. WHEN절에 '특정 컬럼 = 특정 값'을 넣어도 3만개 행을 Full Scan을 한다.

  3. CTE를 사용해서 MERGE 전에 미리 대상 테이블을 필터링하면 Full Scan을 하더라도 속도가 빨라질 수 있다.

 

다음 그림은 조건이 존재함에 따라 대상 테이블과 소스 테이블의 조인 방법과 조인 결과가 무엇인지 나타낸 표이다.

(출처:  https://sites.google.com/site/mssqlserverworkstation/follow-me/blog/insertingupdatinganddeletingdatabyusingmerge)

 

('O'는 지정 됨 / 'X'는 지정되지 않음 / '-'는 상관 없음)

예)

조건이 MATCHED만 있다면 대상 테이블과 소스 테이블을 INNER JOIN 수행

조건이 NOT MATCHED만 있다면 대상 테이블과 소스 테이블을 LEFT JOIN 수행

조건이 NOT MATCHED BY SOURCE만 있다면 대상 테이블과 소스 테이블을 ANTI SEMI JOIN 수행

조건이 3개 다 존재한다면 대상 테이블과 소스 테이블을 FULL JOIN 수행

 

+ ANTI SEMI JOIN이란?

서브 쿼리내에 존재하지 않는 데이터만 추출하여 메인 쿼리에서 추출하는 조인이다. 아래 쿼리는 ANTI SEMI JOIN의 예시이다.

SELECT temp_col
       FROM A a
       WHERE NOT EXISTS (SELECT temp_col FROM B WHERE temp_col = a.temp_col)

 

 

여기까지 MERGE 문에 대해 정리해 보았고, 다음 포스팅에서는 마지막으로 OUTPUT과 APPLY를 함께 다루려고 한다.