In this post, we will discuss on the two new features of SQL SERVER 2005 that are Cross Apply and Outer Apply.

In SQL Server 2005 APPLY operator is used to refer a table-valued function in a correlated sub query.

These operations are similar to SQL Joins operations. SQL joins are used to relate information in different tables whereas Apply clause is used to relate information in a table and a table-valued-function.

APPLY comes in the following two forms:

1) CROSS APPLY:-

CROSS APPLY works like an INNER JOIN. So the result set will contain only those rows that are matched on a particular condition.

For the demo of these operations, we will create some tables with some initial data and a table valued function.

The following script creates two tables table1 and table2 and inserts some intial data into these tables.

CREATE TABLE table1
(
ID int,
Value CHAR(1)
)
go
CREATE TABLE table2
(
ID int,
Detail varchar(10)
)
go
INSERT into table1 select 1,'A' UNION
					select 2,'B' UNION
					select 3,'C' UNION
					select 4,'D' UNION
					select 5,'E'
go
INSERT into table2 select 1,'Apple' UNION
					select 2,'Banana' UNION
					select 3,'Cat' UNION
					select 7,'GOD' UNION
					select 10,'Joker'
					

go

The below script creates a table valued function 'Myfun':

CREATE FUNCTION MyFun(@ID int)
RETURNS Table
as
RETURN
SELECT * FROM table2 WHERE ID=@ID

Now Run the following query to obtain the result produce by CROSS APPLY-

SELECT t.ID,t.Value,ft.Detail FROM table1 t CROSS APPLY MyFun(ID) ft

If you observe the result, you can say that a CROSS APPLY work as an INNER JOIN between a table and a table-valued function..

2) OUTER APPLY:

OUTER APPLY is like an LEFT OUTER JOIN, So the result set will contain all the rows from the left table in conjunction with the matching rows from the table valued function. When  there is no matching row found in the table-valuse function, it returns null value.  

Run the following query and observe the outcome produced by OUTER APPLY-

SELECT t.ID,t.Value,ft.Detail FROM table1 t OUTER APPLY MyFun(ID) ft

Observing the result, we can say that an OUTER APPLY work similar to an LEFT JOIN operation between a table and a table-valued function.