[MSSQL] OUTPUT, APPLY 문법 정리

저번 포스팅에 이어 OUTPUT과 APPLY에 대해 정리하고 마무리 하려고 한다.

 

 

OUTPUT

OUTPUT은 INSERT, UPDATE, DELETE, MERGE문에 의한 데이터 변경 작업의 결과를 작업과 동시에 확인할 수 있다. 원래 SELECT를 해야 작업 결과를 볼 수 있고 다른 DML문(INSERT/UPDATE/DELETE/MERGE)은 영향 받은 행의 숫자만 알려주고 영향 받은 데이터는 보여 주지 않는다. 하지만 OUTPUT 키워드와 inserted, deleted 테이블을 사용하면 영향 받은 데이터도 볼 수 있다. 

 

inserted, deleted 테이블

OUTPUT이나 Trigger에서 DML을 사용할 때 자동으로 변경되는 시스템 테이블로, 기존 테이블과 구조가 동일하다.

INSERT/UPDATE/DELETE/MERGE문 작업을 할 때마다 inserted/deleted 테이블이 새로 만들어진다. 하지만 이 테이블은 메모리에만 존재하기 때문에 금방 사라진다.

 

inserted, deleted 테이블에 들어가는 데이터

사용 예시

INSERT 
    INTO TABLE_A (name, score) 
    OUTPUT inserted.*
    VALUES ('John', 97)

MERGE문은 마지막에 OUTPUT을 추가하면 영향 받은 데이터가 출력으로 나온다.

MERGE TARGET_T target
    USING SOURCE_T AS source (code, name)  
    ON (target.code = source.code)
    WHEN MATCHED THEN
        UPDATE SET name = source.name  
    WHEN NOT MATCHED THEN  
        INSERT (code, name)  
        VALUES (source.code, source.name)  
    OUTPUT deleted.*, inserted.*

참고로, MERGE에서 OUTPUT절을 사용하면 대상 테이블(target table)의 변경 내용을 임의의 순서로 반환한다.

 

INTO

OUTPUT으로 얻은 결과를 INTO [테이블]로 테이블에 넣을 수 있다. 여기서 결과를 넣을 테이블은 이미 존재하는 테이블이어야 한다. OUTPUT 결과를 INTO로 테이블에 넣으면 OUTPUT 결과는 나오지 않고, INTO를 사용하지 않으면 OUTPUT의 결과가 출력된다. 즉, OUTPUT을 사용하면 트리거(Trigger) 없이도 데이터의 변경 사항을 기록할 수 있다.

 

아래 쿼리는 TABLE_A에 데이터를 넣으면 변경된 값을 LOG_TABLE에 추가하는 쿼리로, 변경 사항이 결과로 출력되지 않고 LOG_TABLE에 저장된다. LOG_TABLE은 이미 존재하는 테이블이어야 하므로 미리 테이블을 만들어 놓아야 한다.

INSERT 
    INTO TABLE_A (name, score) 
    OUTPUT inserted.* INTO LOG_TABLE
    VALUES ('John', 97)

 

 

APPLY

테이블에서 얻은 행에 대해 테이블 반환 함수나 하위 쿼리를 반복해서 호출할 수 있다. 즉, 행 마다 테이블 반환 함수 또는 하위 쿼리를 적용한다는 의미이다.

 

CROSS APPLY

• 테이블 반환 함수 또는 하위쿼리의 결과가 있는 행만 출력한다. (=INNER JOIN)

OUTER APPLY

• 테이블 반환 함수 또는 하위쿼리의 결과가 없는 행도 모두 출력한다. (=LEFT JOIN)

장점

• JOIN문과 다르게 오른쪽 테이블이 왼쪽 테이블의 열을 참조할 수 있다.

• NL Join 형태로 동작하기 때문에 아래 예제에서 볼 수 있듯, TOP N문을 사용하여 저장된 데이터량에 상관없이 늘 동일한 비용이 들어간다.

• NL Join에 대해 알고 싶다면 여기에 정리해 놓았으니 참고하길 바란다.

단점

• NL Join 형태로 동작하기 때문에 대량 데이터 처리 시 비용이 높다.

• Join으로 처리가 가능한 경우는 굳이 APPLY 사용을 추천하지 않고, 반드시 필요한 경우에만 제한적으로 사용하기를 권장한다.

 

사용 예시

어떤 글에 대한 정보를 가진 Article 테이블과 글에 달린 댓글에 대한 Article_comment 테이블이 있을 때, 어떤 글을 comment가 가장 많은 순으로 정렬해서 3개만 보여주는 쿼리는 CROSS APPLY를 사용하면 INNER JOIN을 사용했을 때 보다 쿼리 길이도 짧아지고 빠른 성능을 낼 수 있다.

 

두 테이블 구조는 다음과 같다. Article 테이블은 articleNo 인덱스가 있고, Article_comment 테이블은 articleNo, commentNo 인덱스가 있다.

CREATE TABLE Article (
           articleNo  int IDENTITY (1,1),
           title      varchar(1000),
           contents   varchar(1000)
)

CREATE TABLE Article_comment (
           articleNo      int NOT NULL,
           commentNo      int IDENTITY(1,1),
           comments       varchar(1000)
)

 

CROSS APPLY 구문

-- CROSS APPLY
SELECT a.articleNo, a.title, b.commentNo, b.comments
FROM Article a
CROSS APPLY
	( SELECT TOP 3 b.*
		FROM Article_comment b
		WHERE b.articleNo = a.articleNo
		ORDER BY b.commentNo DESC ) b
WHERE a.articleNo < 3

 

INNER JOIN 구문

-- INNER JOIN
SELECT articleNo, title, commentNo, comments
FROM(
	SELECT a.articleNo, a.title, b.commentNo, b.comments, RANK() OVER(PARTITION BY a.articleNo ORDER BY commentNo DESC) rownum
	FROM Article a
	INNER JOIN(
		 SELECT * FROM Article_comment
	) b
	ON a.articleNo = b.articleNo
) o
WHERE rownum <= 3 AND articleNo < 3
ORDER BY articleNo, rownum