APPLY Operator in Sql Server

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)
SELECT * FROM Details WHERE StuId=@StuId

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


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 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.


Author: Dotnet Developer-201524 Jul 2015 Member Level: Gold   Points : 2


Cross apply and inner join
show the output is same so
why we can use cross apply
can you explain for this

outter apply also right output same

why we use this outter apply

can you explain this?

  • 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: