CROSS APPLY and INNER JOIN

發表於 更新於 分類於
AdSense

Foreword

To understand CROSS 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. CROSS APPLY is equal to INNER JOIN in the simple condition

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

1
2
3
4
5
6
7
-- INNER JOIN
SELECT *
FROM tmpDepartment d
INNER JOIN (
	SELECT *
	FROM tmpEmployee e) e2
ON d.departmentID = e2.departmentID
1
2
3
4
5
6
7
-- CROSS APPLY
SELECT *
FROM tmpDepartment d
CROSS 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.

INNER JOIN

This is an incorrect expression. I cannot only add ORDER BY clause in a derived table but not add TOP N.

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

ERROR: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The result from the following query is not what I want. This query just gets the latest two employees.

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

Try to update query expression. However, this is an incorrect expression.

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

ERROR: Incorrect syntax near ‘e2’.
We can’t refer to an outer query column(d.departmentID) inside a using a join.

CROSS APPLY

INNER JOIN cannot work whereas CROSS APPLY can get the result what I want well.

1
2
3
4
5
6
7
8
-- CROSS APPLY
SELECT *
FROM tmpDepartment d
CROSS 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)

Cross 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. INNER 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)

INNER 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
-- INNER JOIN
SELECT *
FROM tmpDepartment d
INNER 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.

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

The query above can work well since 1 is not outer query column for tmpEmployee in GetAllEmployeeForDepartment(@deptID).

CROSS APPLY

INNER JOIN cannot work so try to use CROSS APPLY.

1
2
3
4
-- CROSS APPLY
SELECT *
FROM tmpDepartment d
CROSS 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)

INNER JOIN

INNER JOIN cannot work as above.

CROSS APPLY

1
2
3
4
-- CROSS APPLY
SELECT *
FROM tmpDepartment d
CROSS 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:
SQL Cross Apply and Outer Apply: The Complete Guide
CROSS APPLY in Sql Server