[MS-SQL] OUTER APPLY 와 CROSS APPLY 의 사용 방법 예제 및 APPLY 연산자를 사용하는 이유

떠돌이개발자Aug 25, 2023

본론으로 먼저 들어가보자.

CROSS APPLY는 INNER JOIN 동일한 쿼리 실행 결과와 동일하며, OUTER APPLY는 LEFT JOIN과 동일한 쿼리 실행 결과와 동일하다.

그렇다면 APPLY 연산자를 언제, 왜 사용하는 것일까?

일반적으로 JOIN을 사용하여 동일한 결과를 얻을 수 있는데 말이다.

그 이유는 이렇다.

첫번째로 APPLY의 경우 테이블 반환 함수를 생성하여 조인해야할 경우에 사용한다. 만약 테이블 반환 함수와 JOIN을 사용하면 오류가 발생한다. 두번째로 APPLY 연산자를 사용하면 쿼리 성능이 향상된다고 한다.

스칼라 서브쿼리를 사용하여 공통코드의 이름을 가져오는 것 보다 APPLY 연산자를 사용하여 가져오는게 쿼리 성능면에서 좋다.

그렇다면 그 내용을 살펴보기 위해 테이블을 생성하고 쿼리를 실행해보자. 구문식은 다음과 같다.

SELECT *
FROM [테이블]
CROSS | OUTER APPLY (SELECT * FROM [테이블] WHERE [조건자])

1. CROSS APPLY 및 OUTER APPLY 테스트를 위한 샘플 데이터 생성

--USE [tempdb] 
GO
 
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Employee] 
END 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Department] 
END 

CREATE TABLE [Department]( 
   [DepartmentID] [int] NOT NULL PRIMARY KEY, 
   [Name] VARCHAR(250) NOT NULL, 
) ON [PRIMARY] 

INSERT [Department] ([DepartmentID], [Name])  
VALUES (1, N'Engineering') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (2, N'Administration') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (3, N'Sales') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (4, N'Marketing') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (5, N'Finance') 
GO 

CREATE TABLE [Employee]( 
   [EmployeeID] [int] NOT NULL PRIMARY KEY, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL, 
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID), 
) ON [PRIMARY] 
GO
 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (1, N'Orlando', N'Gee', 1 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (2, N'Keith', N'Harris', 2 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (3, N'Donna', N'Carreras', 3 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (4, N'Janet', N'Gates', 3 )

2. CROSS APPLY 와 INNER JOIN 예제

SELECT * FROM Department D 
CROSS APPLY 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 

--inner join 예제 
SELECT * FROM Department D 
INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID

[쿼리 실행결과]

다음과 같이 동일하다.

또한 아래 이미지에서 볼 수 있듯이 쿼리의 실행 계획(execution plans) 역시 동일하고 쿼리 비용(query cost) 역시 동일합니다.

3. OUTER APPLY 대 LEFT OUTER JOIN 예제

SELECT * FROM Department D 
OUTER APPLY 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 

-- LEFT 조인 
SELECT * FROM Department D 
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID 
GO

[쿼리 실행결과]

다음과 같이 리턴되는 결과 값이 동일하다.

쿼리 결과는 동일하지만 실행계획은 약간 다르다.

비용 측면에서는 큰 차이가 없지만

OUTER APPLY를 사용한 쿼리는 Nested Loops 연산자보다 먼저 Compute Scalar 연산자(예상 연산자 비용 0.0000103 또는 약 0%)를 사용하여 Employee 테이블의 컬럼을 평가하고 생성한다.

4. APPLY 연산자를 사용하여 테이블 반환 함수와 테이블 조인 예제

DepartmentID를 매개변수로 받는 부서에 속한 모든 직원을 반환하는 테이블 반환 함수(fn_GetAllEmployeeOfADepartment )를 만든다.

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF')) 
BEGIN 
   DROP FUNCTION dbo.fn_GetAllEmployeeOfADepartment 
END 
GO
 
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO

CROSS APPLY 와 OUTER APPLY를 사용하여 Department 테이블과 조인하여 조회한 결과이다.

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 
GO
 
SELECT * FROM Department D 
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 
GO

쿼리 실행계획은 다음과 같다.

다음은 위 실행계획의 내부조인(비용 0%) 과 LEFT JOIN(비용 1%)의 결과 정보이다.

5. CROSS APPLY 연산자를 사용한 UNPIVOT 예제

--DROP TABLE #temp_survey
select * into #temp_survey from (
 select '홍길동' AS UserName, '너의 성은?' AS Q1, '홍' AS A1, '나이는?' AS Q2, '50' AS A2, '어디살아?' AS Q3, '경상도' AS A3
 UNION ALL
 select '김영수' AS UserName, '너의 성은?' AS Q1, '김' AS A1, '나이는?' AS Q2, '30' AS A2, '어디살아?' AS Q3, '전라도' AS A3
 UNION ALL   
 select '아이유' AS UserName, '너의 성은?' AS Q1, '아' AS A1, '나이는?' AS Q2, '20' AS A2, '어디살아?' AS Q3, '서울' AS A3
 UNION ALL         
 select '한지민' AS UserName, '너의 성은?' AS Q1, '한' AS A1, '나이는?' AS Q2, '40' AS A2, '어디살아?' AS Q3, '부산' AS A3
) TA


-- Data 원본 조회
select * from #temp_survey

 

/* Unpvt with CrossApply */
select UserName, CrossApplied.Question, CrossApplied.Answer
from #temp_survey
cross apply (VALUES (Q1, A1), (Q2, A2), (Q3, A3)) CrossApplied (Question, Answer)

Data 원본 조회 결과가 다음과 같을 때