OUTER APPLY and LEFT (OUTER) JOIN

發表於 更新於 分類於
AdSense

Foreword

To understand OUTER APPLY in MS SQL Server with extensive list of examples, I create two simple tables with sample data as the followings. The script of creating the tables is here.

Join a table to a derived table that refers to the outer query

1. OUTER APPLY is equal to LEFT JOIN in the simple condition

From the result perspective we can say that the OUTER APPLY is similar to the classic LEFT JOIN here. Here, I want to list all employees in the all departments.

1
2
3
4
5
6
7
-- LEFT JOIN
SELECT *
FROM tmpDepartment d
LEFT JOIN (
	SELECT *
	FROM tmpEmployee e) e2
ON d.departmentID = e2.departmentID
1
2
3
4
5
6
7
-- OUTER APPLY
SELECT *
FROM tmpDepartment d
OUTER APPLY (
	SELECT *
	FROM tmpEmployee e
	WHERE d.departmentID = e.departmentID) e2

2. Different results in more conditions

Here, I want to get the latest two employees of the each department. List NULL if there is no data.

LEFT JOIN

The result from the following query is not what I want. This query just gets the latest two employees and includes the departments with NULL value.

1
2
3
4
5
6
7
8
-- LEFT JOIN
SELECT *
FROM tmpDepartment d
LEFT JOIN (
	SELECT TOP 2 *
	FROM tmpEmployee e
	ORDER BY startDate DESC) e2
ON d.departmentID = e2.departmentID

OUTER APPLY

LEFT JOIN cannot work whereas OUTER APPLY can get the result what I want well.

1
2
3
4
5
6
7
8
-- OUTER APPLY
SELECT *
FROM tmpDepartment d
OUTER APPLY (
	SELECT TOP 2 *
	FROM tmpEmployee e
	WHERE d.departmentID = e.departmentID
	ORDER BY startDate DESC) e2

Join a table to a Table-Valued Function(TVF)

Outer Apply lets me join a table to a Table-valued Function.

Here, I want to do the same thing: listing all employees in the all departments. However, I would like to use TVF to get the result this time.

1. LEFT JOIN cannot work even in the simple condition

Table-valued Function

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION GetAllEmployeeForDepartment(@deptID AS INT)  
RETURNS TABLE 
AS 
RETURN ( 
   SELECT * FROM tmpEmployee e 
   WHERE e.departmentID = @deptID
   )

select * from GetAllEmployeeForDepartment(1)
select * from GetAllEmployeeForDepartment(3)

LEFT JOIN

This is an incorrect expression. The reason of not being able to use d.departmentID is d.departmentID means outer query column for tmpEmployee in GetAllEmployeeForDepartment(@deptID).

1
2
3
4
5
-- LEFT JOIN
SELECT *
FROM tmpDepartment d
LEFT JOIN GetAllEmployeeForDepartment(d.departmentID) e_for_d
ON d.departmentID = e_for_d.departmentID

ERROR: The multi-part identifier “d.departmentID” could not be bound.

OUTER APPLY

LEFT JOIN cannot work so try to use OUTER APPLY.

1
2
3
4
-- OUTER APPLY
SELECT *
FROM tmpDepartment d
OUTER APPLY GetAllEmployeeForDepartment(d.departmentID) e_for_d

2. More conditions: listing the latest two employees of the each department

Table-valued Function

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION GetTop2EmpForDep(@deptID AS INT)  
RETURNS TABLE 
AS 
RETURN ( 
   SELECT TOP 2 * FROM tmpEmployee e 
   WHERE e.departmentID = @deptID
   ORDER BY e.startDate DESC
   ) 

select * from GetTop2EmpForDep(1)
select * from GetTop2EmpForDep(3)

LEFT JOIN

LEFT JOIN cannot work as above.

OUTER APPLY

1
2
3
4
-- OUTER APPLY
SELECT *
FROM tmpDepartment d
OUTER APPLY GetTop2EmpForDep(d.departmentID) e_for_d

Why I Use CROSS APPLY or OUTER APPLY in SQL?

If I want to add a derived table including an outer query/a column of a table, I have to use Cross Apply or Outer Apply. Inner Join and Left Join cannot work.

If you would like to read more about APPLY operator, here it is - SQL Server APPLY operator.

References:
OUTER APPLY in Sql Server