APPLY Operator

Apply operator is of two types: Cross Apply and outer apply. Cross Apply is more like left outer join and Outer apply is more like outer join. Below we will see by an example as how it works and can be helpful in querying the table valued function. We will use AdventureWorks database for this example.

Apply operator can be leveraged to query table valued function and results a row once per every row of outer table.

Let's create the table valued function first:

Create FUNCTION getHighestQuantity

(

    @num int

    ,@prodID varchar(20)

)

RETURNS TABLE

AS

RETURN

(

    select distinct top(@num) p.ListPrice ,p.Name,color,OrderQty

from SalesLT.SalesOrderDetail od inner join

SalesLT.Product p

ON od.ProductID =p.ProductID

where p.ProductID like '%'+@prodID+'%'

order by OrderQty desc

)

GO

This is a simple table valued function that will return details of product that sold highest quantity.

Query the table valued function using cross apply

select gt.*,StandardCost

from dbo.Products p cross apply dbo.getHighestQuantity(10,p.ProductID )gt

where gt.color='orange'

--In this we see that for every row of the table is an argument to the table valued function