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)
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.
Hi
Krithiga
Cross apply and inner join
show the output is same so
why we can use cross apply
can you explain for this
scenario
outter apply also right output same
why we use this outter apply
can you explain this?