SQLSERVER CROSS APPLY 與 OUTER APPLY 的應用

標籤：student   成績表   join   inner   根據   out   學生   也會   sql   

日常開發中遇到多表查詢時，首先會想到 INNER JOIN 或 LEFT OUTER JOIN 等等，但是這兩種查詢有時候不能滿足需求。比如，左表一條關聯右表多條記錄時，我需要控制右表的某一條或多條記錄跟左表匹配。貌似，INNER JOIN 或 LEFT OUTER JOIN 不能很好完成。但是 CROSS APPLY 與 OUTER APPLY 可以

有兩張表：Student（學生表）和 Score（成績表），資料如下

1)查詢每個學生最近兩次的考試成績

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1

CROSS APPLY(

    SELECT TOP 2 * FROM Score AS T

    WHERE T1.StudentNo = T.StudentNo

    ORDER BY T.ExamDate DESC

) AS T2

2)  查詢每個學生最近兩次的考試成績,沒有參加考試的同學成績補 null

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1

OUTER APPLY(

    SELECT TOP 2 * FROM Score AS T

    WHERE T1.StudentNo = T.StudentNo

    ORDER BY T.ExamDate DESC

) AS T2


 總結

1.   理解 CROSS APPLY 與 OUTER APPLY（個人理解）

1)   CROSS APPLY 的意思是“交叉應用”，在查詢時首先查詢左表，然後右表的每一條記錄跟左表的目前記錄進行匹配。匹配成功則將左表與右表的記錄合并為一條記錄輸出；匹配失敗則拋棄左表與右表的記錄。（與 INNER JOIN 類似）

2)   OUTER APPLY 的意思是“外部應用”，與 CROSS APPLY 的原理一致，只是在匹配失敗時，左表與右表也將合并為一條記錄輸出，不過右表的輸出欄位為 null。（與 LEFT OUTER JOIN 類似）

 

2.   CROSS APPLY 與 INNER JOIN 的區別

1)   CROSS APPLY 可以根據當前左表的目前記錄去查詢右表，但是 INNER JOIN 不可以，INNER JOIN 是根據左表的目前記錄匹配右表整個結果集。

2)   兩者都是匹配成功才輸出。

 

3.   OUTER APPLY 與 LEFT OUTER JOIN 的區別

1)   它們和（CROSS APPLY 與 INNER JOIN）類似。

2)   只是兩者都是匹配失敗也會輸出。

 

4.   使用情境：

1)   一個商品有多張圖片，但是只想取最近的一張圖片跟商品匹配。

 

5.   總結一句話：右表可以是有條件的跟左表的記錄匹配，而條件的值可以來至於左表。

SQLSERVER CROSS APPLY 與 OUTER APPLY 的應用