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.
APPLY operator doesn’t need to have predefined table from both sides! Image below can help you to understand how it works:
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.
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.
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!
