In this article, we are learning the use of the CROSS APPLY and OUTER APPLY
SQL Server APPLY operator has two variants: CROSS APPLY and OUTER APPLY
- The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.
- The OUTER APPLY operator returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression. So you might conclude, the CROSS APPLY is equivalent to an INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas the OUTER APPLY is equivalent to a LEFT OUTER JOIN.
- You might be wondering if the same can be achieved with a regular JOIN clause, so why and when do you use the APPLY operator? Although the same can be achieved with a normal JOIN, the need of APPLY arises if you have a table-valued expression on the right part and in some cases the use of the APPLY operator boosts performance of your query. Let me explain with some examples.
Below is the Sample Example for the CROSS APPLY and OUTER APPLY
Script #1: Create and Insert some Data in Tables
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)
SELECT * FROM department
SELECT * FROM employee
SQL Server CROSS APPLY vs INNER JOIN example
Script #2: CROSS APPLY vs INNER JOIN
SELECT *
FROM department D
CROSS apply (SELECT *
FROM employee E
WHERE E.departmentid = D.departmentid) A
go
SELECT *
FROM department D
INNER JOIN employee E
ON D.departmentid = E.departmentid
go
SQL Server OUTER APPLY vs LEFT OUTER JOIN example
Script #3: OUTER APPLY vs LEFT OUTER JOIN
SELECT *
FROM department D
OUTER apply (SELECT *
FROM employee E
WHERE E.departmentid = D.departmentid) A
go
SELECT *
FROM department D
LEFT OUTER JOIN. employee E
ON D.departmentid = E.departmentid
go
Joining table valued functions and tables using APPLY operators
Script #4: Joining table valued functions with APPLY Operators
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
SELECT *
FROM department D
CROSS apply dbo.Fn_getallemployeeofadepartment(D.departmentid)
go
SELECT *
FROM department D
OUTER apply dbo.Fn_getallemployeeofadepartment(D.departmentid)
go
You might be wondering if we can use a simple join in place of the above queries, the answer is NO. If you replace the CROSS/OUTER APPLY in the Script #4 with an INNER JOIN/LEFT OUTER JOIN, specifying the ON clause with 1=1 and run the query, you will get the error “The multi-part identifier “D.DepartmentID” could not be bound.”. This is because with JOINs the execution context of the outer query is different from the execution context of the function (or a derived table), and you cannot bind a value/variable from the outer query to the function as a parameter. Hence the APPLY operator is required for such queries. So in summary the APPLY operator is required when you have to use a table-valued function in the query, but it can also be used with inline SELECT statements.
