Sql Server APPLY Function Tutorial and Examples

Description:	APPLY's work the same as inner and outer joins. They additionally support table value functions and nested subqueries returning multiple rows . Cross Apply: Works the same as an inner join. Outer Apply: Works the same as an outer join.
Sql Server Version:	2005+.

Note: This sample uses the AdventureWorks database which can be downloaded here: 2005 or 2008

Sample Cross Apply Query:

1. Create a table-valued function: This is not required for a Cross Apply but is used to demonstrate the Cross Apply flexibility

create function dbo.GetEmployeeCity(@employeeID int)

returns table

return

select addr.City, addr.PostalCode

from HumanResources.EmployeeAddress ea

inner join Person.Address addr on addr.AddressID = ea.AddressID

where ea.EmployeeID = @employeeID;

2. Create the Cross Apply Query

select emp.LoginID, convert(varchar,b.StartDate,101) Startdate,

convert(varchar,b.EndDate,101) EndDate, emp.Title, d.*

from HumanResources.Employee emp

CROSS APPLY

(select * from HumanResources.EmployeeDepartmentHistory as sdh

CROSS APPLY(select shift.Name as Shift

from humanresources.Shift shift

where sdh.ShiftID = shift.ShiftID

and shift.ShiftID = 1) a

where emp.EmployeeID = sdh.EmployeeID

and sdh.EndDate is not null) b

CROSS APPLY GetEmployeeCity(emp.EmployeeID) d

Results: