Few words about: APPLY operator

APPLY operator is very useful feature that is not very common, so I would like to introduce you into one of the most powerful tools in TSQL.

So let’s begin by showing some theory about JOIN operators. INNER JOIN and OUTER JOINS (RIGHT,LEFT, FULL) are very common and they work in very similar manner, as you can see in the picture below, JOINS must have two sets that are predefined – query engine have to know about rows from both sides before it executes the query.

Image

APPLY operator doesn’t need to have predefined table from both sides! Image below can help you to understand how it works:

Image

Apply operator requires predefined table from left side. So if you want to calculate table valued function row by row you can’t use JOINS (function result is not predefined) but you can use APPLY. So if you want to proceed something row by row – don’t use cursors – use APPLY! Let’s some TSQL code. We will use AdventureWorks, first of all we’ve got our testing business scenario: we need to have a query that dynamically return N last order dates per customer. First of all we create table valued function that return this data:

CREATE FUNCTION [dbo].[test]

(

   @custId int,

   @topn int

)

RETURNS TABLE

AS

RETURN

SELECT distinct TOP (@topn) dc.CustomerKey,fis.OrderDate FROM AdventureWorksDW2012.dbo.DimCustomer dc

JOIN AdventureWorksDW2012.dbo.FactInternetSales fis

ON fis.CustomerKey=dc.CustomerKey

WHERE dc.CustomerKey=@custId

I think this definition is clear so I don’t write how it works. After that we need to test it:

 

select * from dbo.test(11001,2)

it returns two last order dates for customer with id 11001.

Image

Ok, our statement works properly. And the final step is using CROSS APPLY function:

SELECT dc.CustomerKey,t.OrderDate

FROM AdventureWorksDW2012.dbo.DimCustomer dc

CROSS APPLY dbo.test(dc.CustomerKey,2) t

ORDER BY 1,2 desc

Cross apply logically works similar to the Inner join, it returns only rows that have matches in both sides.

Image

It looks great, to our function we provide CustomerKey (current row customer key) and how much last orders we need. Ok but what if some clients don’t have any orders – as I said before CROSS APPLY returns only rows that match in both tables – so we need to have clause similar to OUTER JOIN and of course we have got something like this and it’s called OUTER APPLY. If you download AdventureWorks2012DW there is no customer that doesn’t have any orders so you can add a row to the DimCustomer and run this query – I add where clause to show you that this query returns also customers without orders

SELECT dc.CustomerKey,t.OrderDate

FROMAdventureWorksDW2012.dbo.DimCustomer dc

OUTER APPLY dbo.test(dc.CustomerKey,2) t

WHERE OrderDate IS NULL

ORDER BY 1,2 DESC

Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s