APPLY (OUTER / CROSS)

CROSS APPLY 와 JOIN  차이점 : CROSS APPLY (table-valued expression 사용가능)

-- 1. CROSS APPLY = INNER JOIN 

SELECT * FROM MEMBER AS A

CROSS APPLY 

(

SELECT * FROM EVENT_History WHERE Event='2016-1회'

AND USERID = A.USERID

) AS B

SELECT * FROM MEMBER AS A

INNER JOIN EVENT_History AS B

ON A.UserID = B.UserID 

AND B.Event='2016-1회'

>> 동일 결과 

-- 2. OUTER APPLY = LEFT OUTER JOIN

SELECT * FROM MEMBER AS A

OUTER APPLY 

(

SELECT * FROM TBL_WWW_EVENT_History WHERE EventTYPE='2016-1회'

AND USERID = A.USERID

) AS B

SELECT * FROM MEMBER AS A

LEFT OUTER JOIN EVENT_History AS B

ON A.UserID = B.UserID 

AND B.Event='2016-1회'

>> 동일 결과 

-- 차이점 

-- 3.1 apply 는 function 사용 가능

SELECT * FROM MEMBER A

CROSS APPLY -- OUTER APPLY

[dbo].[Ranking_iTVF](A.USERID,'2016-06-01','2016-06-30') B

SELECT * FROM MEMBER A

LEFT OUTER JOIN

[dbo].[Ranking_iTVF](A.USERID,'2016-06-01','2016-06-30') B

ON A.UserID =B.USERID