APPLY Operator in Sql Server

Posted Date: 10-Sep-2012	Category: SQL Server
Author: kirthiga	Member Level: Gold	Points: 20

In this article I explain about what APPLY Operator is and where to use in SQL Server. APPLY operator allows you to join a table with table valued function. The APPLY operator is used in the FROM clause between a left table source and a right table source. CROSS APPLY and OUTER APPLY are the two forms of APPLY operator.

The APPLY operator invokes a table-valued function for each row returned by an outer table. The list of columns produced by the APPLY operator is the set of columns evaluated against the table valued function for each row in the outer table.

The table valued function is not only a function that returns a table data type it can be an Inline table value function has the result set of a parameter driven by a Select statement.

To use the APPLY operator in SQL Server the database compatibility level needs to be set to 90.

Syntax for APPLY Operator


Select [column list] 
from Left_Table_Source [{Cross/Outer}APPLY] Right_Table_Source

Let us see the working of APPLY operators with an example.


Create table Student(StuId int ,Name varchar(10))
Create table Details(Sno int identity ,StuId int  ,DateOfJoining datetime)

Insert into Student values(1001,'Ram'),(1002,'Surya'),(1003,'Sam')
Insert into Details values(1001,'2011-05-24'),(1002,'2011-05-28')

select * from Student
select * from Details

Here I created two tables Student and Details with two and three rows simultaneously for each table.


CREATE FUNCTION Fn_Student_Details(@StuId int)
RETURNS TABLE
AS 
RETURN
(
SELECT * FROM Details WHERE StuId=@StuId
)

The above function Fn_Student_Details will return a table as output based on StuId.

CROSS APPLY

CROSS APPLY calls a function for each row return by a table.


select S.StuId,S.Name,DateOfJoining 
from Student S cross apply Fn_Student_Details(S.StuId)

StuId	Name	DateOfJoining
-----   ----    -------------------
1001	Ram	2011-05-24 00:00:00.000
1002	Surya	2011-05-28 00:00:00.000

Here Student table is a Left table source and Function Fn_Student_Details is a Right table source. For each StuId from left table source is evaluated against the Right table source. CROSS APPLY returns only the rows that produce a result set from the Right table source.

Hence for each StuId from Student table we get the output. We can simply mention that CROSS APPLY as INNER JOIN between a table and a table valued function.

OUTER APPLY

OUTER APPLY is similar to that of CROSS APPLY. The only difference is that CROSS APPLY will not return rows from the Left table source if Right table sorce has no corresponding records on it. OUTER APPLY returns NULL values for the columns produced by the Right table source.


select S.StuId,S.Name,DateOfJoining 
from Student S outer apply Fn_Student_Details(S.StuId)

StuId	Name	DateOfJoining
-----   ----    --------------------
1001	Ram	2011-05-24 00:00:00.000
1002	Surya	2011-05-28 00:00:00.000
1003	Sam	NULL

Thus we compare OUTER APPLY as LEFT JOIN between a table and a table valued function.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.

Online Members

APPLY Operator in Sql Server

Syntax for APPLY Operator

CROSS APPLY

OUTER APPLY

Post Comment:

Email subscription