CROSS APPLY and OUTER APPLY is new in Microsoft SQL Server 2005. CROSS APPLY works like JOIN and a.k.a. INNER APPLY. You can use it like co-related query.
–create one table for demo
use adventureworks
–department table
CREaTE TABLE deptInfo
(
DeptName VARCHAR(10),
Description VARCHAR(20)
)
–INSERT records
INSERT INTO DeptInfo
SELECT ‘MIS’,‘IT DEPT’ UNION ALL
SELECT ‘account’,‘finance department’ UNION ALL
SELECT ‘chemical’,‘chemical department’
GO
CREATE TABLE emps
(
Name VARCHAR(50),
Dept VARCHAR(10),
Company VARCHAR(15)
)
–INSERT records
INSERT INTO emps
SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL
SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL
SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL
SELECT ‘Alka’,‘account’,‘tata’ UNION ALL
SELECT ‘Alpesh’,‘Chemical’,‘echem’
GO
–creating inline table valued function
CREATE FUNCTION dbo.GetEmployeeData(@dept VARCHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT * FROM emps WHERE dept=@dept
)
GO
–use cross apply in above function
SELECT d.Deptname,d.description,e.name,e.company FROM DeptInfo d
CROSS APPLY
dbo.getemployeedata(d.deptname) as e
WHERE d.deptname=‘MIS’
Note: You can refer another article on Cross Apply and Outer apply at:
http://www.sqlhub.com/2009/03/cross-apply-and-outer-apply-clause-in.html
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com