MSSQL OUTER APPLY, CROSS APPLY 예제, 설명
출처 : https://goldswan.tistory.com/16 금백조의 개발 블로그
서론
시스템 유지보수를 진행하면서 OUTER APPLY를 사용하는 쿼리를 접해본 적이 있습니다. 당시에는 LEFT JOIN과 동일한 결과를 반환할 수 있다는 정도만 알았었는데 이번 기회에 APPLY 연산자에 대해 좀 더 자세히 알고자 글을 작성했습니다.
본론
[구문 형식]
SELECT ..
FROM [외부 테이블]
CROSS | OUTER APPLY (SELECT .. FROM [내부 테이블] WHERE [조인 조건자])
[정의]
[CROSS APPLY]
내부 테이블(테이블 반환 함수)의 집합으로부터 조인 키로 결합한 외부 테이블의 행만을 반환합니다.
[OUTER APPLY]
내부 테이블(테이블 반환 함수)의 집합으로부터 조인 키에 결합한 외부 테이블의 행 + 그렇지 않은 행 전부를 반환합니다. 그렇지 않은 행에 열에는 NULL을 표시합니다.
[특징]
1. CROSS APPLY는 INNER JOIN, OUTER APPLY는 LEFT JOIN과 동일한 결과 집합을 반환할 수 있습니다.
2. JOIN과 동일한 결과 집합을 반환하더라도 상황에 따라 옵티마이저의 실행계획이 다를 수 있습니다.
[장점]
1. 오른쪽 서브 쿼리(내부 테이블)에 외부 테이블의 컬럼을 인자로 제공할 수 있습니다.
2. 테이블 반환 함수도 조인하여 사용할 수 있습니다.(JOIN연산자로는 불가능)
3. 특정 상황에는 JOIN보다 더 좋은 성능을 발휘할 수 있습니다.
(아래의 [OUTER APPLY 예제 2]처럼 APPLY와 TOP을 함께 사용하면 더욱 좋은 성능을 기대할 수 있습니다.)
[주의사항]
1. SQL Server 2005 이상에서 사용 가능합니다.
2. 조인 조건자를 주지 않을 경우 Cartesian Product(카티션 프로덕트)로 동작하니 주의가 필요합니다.
[예제 실습을 위한 테스트 데이터 생성]
--CUSTOMER(고객) 테이블 생성
CREATE TABLE CUSTOMER(
ID INT IDENTITY(1,1) PRIMARY KEY--아이디
,NAME NVARCHAR(30)--이름
)
--CUSTOMER_PURCHASE(고객 구매 리스트) 테이블 생성
CREATE TABLE CUSTOMER_PURCHASE(
PURCHASE_NO INT IDENTITY(1,1) PRIMARY KEY--구매번호
,ID INT FOREIGN KEY REFERENCES CUSTOMER(ID)--아이디
,ITEM NVARCHAR(30)--품목
,QTY INT--수량
,PRICE INT--가격
)
--ID입력시 [NAME]을 반환해주는 FN_GET_CUSTOMER_NAME 함수 생성
CREATE FUNCTION FN_GET_CUSTOMER_NAME(@ID INT)
RETURNS TABLE
AS
RETURN
(
SELECT [NAME] FROM CUSTOMER(NOLOCK) WHERE ID = @ID
)
--데이터 생성
INSERT CUSTOMER VALUES ('ALICE')
INSERT CUSTOMER VALUES ('BOB')
INSERT CUSTOMER VALUES ('SAM')
INSERT CUSTOMER_PURCHASE VALUES (1,'ITEM1',1,1000)
INSERT CUSTOMER_PURCHASE VALUES (1,'ITEM2',1,2000)
INSERT CUSTOMER_PURCHASE VALUES (1,'ITEM3',1,3000)
INSERT CUSTOMER_PURCHASE VALUES (2,'ITEM1',2,2000)
INSERT CUSTOMER_PURCHASE VALUES (2,'ITEM2',3,6000)
INSERT CUSTOMER_PURCHASE VALUES (2,'ITEM4',1,4000)
INSERT CUSTOMER_PURCHASE VALUES (2,'ITEM5',1,5000)
--고객 정보 확인
SELECT * FROM CUSTOMER
--고객 구매 리스트 정보 확인
SELECT * FROM CUSTOMER_PURCHASE
[CROSS APPLY 예제 1]
--CROSS APPLY를 이용하여 CUSTOMER와 조인 후 NAME 표시
SELECT A.PURCHASE_NO, A.ID, B.[NAME], A.ITEM, A.QTY, A.PRICE
FROM CUSTOMER_PURCHASE A
CROSS APPLY (SELECT * FROM CUSTOMER B WHERE A.ID = B.ID) B
--동일한 결과 반환하는 INNER JOIN
--SELECT A.PURCHASE_NO, A.ID, B.[NAME], A.ITEM, A.QTY, A.PRICE
--FROM CUSTOMER_PURCHASE A
--INNER JOIN CUSTOMER B ON A.ID = B.ID
[CROSS APPLY 예제 2]
--CROSS APPLY를 이용하여 테이블 반환 함수에 왼쪽 테이블의 컬럼(A.ID)을 인자로 전달
SELECT A.PURCHASE_NO, A.ID, B.[NAME], A.ITEM, A.QTY, A.PRICE
FROM CUSTOMER_PURCHASE A
CROSS APPLY (SELECT [NAME] FROM FN_GET_CUSTOMER_NAME(A.ID) B) B
--JOIN으로는 불가능(동작 X)
--SELECT A.PURCHASE_NO, A.ID, B.[NAME], A.ITEM, A.QTY, A.PRICE
--FROM CUSTOMER_PURCHASE A
--INNER JOIN FN_GET_CUSTOMER_NAME(A.ID) B ON A.ID = B.ID
[OUTER APPLY 예제 1]
--OUTER APPLY를 이용하여 고객 이름과 구매 품목 정보 추출
SELECT A.[NAME], B.ITEM
FROM CUSTOMER A
OUTER APPLY (SELECT * FROM CUSTOMER_PURCHASE B WHERE A.ID = B.ID) B
--LEFT JOIN과 동일한 결과 반환
--SELECT A.[NAME], B.ITEM
--FROM CUSTOMER A
--LEFT JOIN CUSTOMER_PURCHASE B ON A.ID = B.ID
[OUTER APPLY 예제 2]
--CUSTOMER_PURCHASE(고객 구매 리스트)에 구매 기록이 없는 고객 정보 추출
--OUTER APPLY + TOP + Correlated Subquery 이용
--TOP 1을 사용하여 필요한 인덱스 키(B.ID)에만 엑세스하여 성능 향상을 기대할 수 있음.
SELECT A.ID, A.[NAME]
FROM CUSTOMER A
OUTER APPLY (SELECT TOP 1 ID FROM CUSTOMER_PURCHASE B WHERE A.ID = B.ID) B
WHERE B.ID IS NULL
--동일한 결과 반환하는 LEFT JOIN
--LEFT JOIN으로 할경우
--인덱스 키(B.ID)에 전체 엑세스하게 되므로 성능이 떨어질 수 있음.
--SELECT A.ID, A.[NAME]
--FROM CUSTOMER A
--LEFT JOIN CUSTOMER_PURCHASE B ON A.ID = B.ID
--WHERE B.ID IS NULL
결론
APPLY 연산자의 장점을 잘 활용하면 JOIN 연산자로 작성된 쿼리를 APPLY 연산자로 바꿔서 성능을 향상할 수 있을 것으로 기대됩니다. 또한 복잡한 SQL 구문을 더욱 간단하게 작성하는데 도움을 줄 것이라고 생각됩니다. 실제 프로젝트에서도 APPLY 연산자를 사용할만한 상황이 생기면 적극적으로 도입해봐야겠습니다.
Reference
[FROM 절과 JOIN, APPLY, PIVOT(Transact-SQL)]
https://docs.microsoft.com/ko-kr/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15
[SQL Server 교차 적용 및 외부 적용]
https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
[SQL Server apply 쿼리 사용방법과 join 쿼리와의 차이점]
https://tipntech.tistory.com/20
[차집합 구하기, 어떤 쿼리가 좋을까?]
출처: https://goldswan.tistory.com/16 [금백조의 개발 블로그]
'Web-Programming > MSSQL' 카테고리의 다른 글
| [DBMS ] 무료 데이터베이스 관리 툴 추천 - DBeaver Community (0) | 2021.11.24 |
|---|
댓글