LessThanDot

In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at CROSS APPLY and OUTER APPLY. In yesterday’s JOIN post I touched upon CROSS APPLY briefly today I want to took a closer look

The APPLY operator is similar to a CROSS JOIN however you can return different set of rows for each row in the left table. First create this table

CREATE TABLE Test1(id INT)
go
 
INSERT Test1 VALUES(1)
INSERT Test1 VALUES(2)
INSERT Test1 VALUES(3)
INSERT Test1 VALUES(4)
INSERT Test1 VALUES(5)
 
 
CREATE TABLE Test2(id int,SomeID INT)
go
 
INSERT Test2 VALUES(1,1)
INSERT Test2 VALUES(1,2)
INSERT Test2 VALUES(1,3)
INSERT Test2 VALUES(2,1)
INSERT Test2 VALUES(2,2)
INSERT Test2 VALUES(2,3)
INSERT Test2 VALUES(3,1)
INSERT Test2 VALUES(3,2)
INSERT Test2 VALUES(3,3)

If you want to do a cartesian product, you can use one of these two queries, they return identical data, for each row in one table all the rows in the other tavle are returned, you will get back 45 rows

SELECT * FROM Test1 t1
CROSS APPLY Test2 t2

SELECT * FROM Test1 t1
CROSS JOIN Test2 t2

Let’s say I want to return all rows in table Test1, join that with all rows in table Test2 but I want only the two top rows in table Test2 ordered by SomeID descending

So from table Test2, what we want back is this

Here is how you can do that by using the CROSS APPLY operator

SELECT * FROM Test1 t1
CROSS APPLY (SELECT TOP(2) Someid,ID FROM Test2 t2
WHERE t2.id = t1.id
ORDER BY Someid DESC) x

id Someid  ID
1   3   1
1   2   1
2   3   2
2   2   2
3   3   3
3   2   3

If you want to return all rows from table Test1 regardless if they exist in table Test2, you can change CROSS APPLY to OUTER APPLY

SELECT * FROM Test1 t1
OUTER APPLY (SELECT TOP(2) Someid,ID FROM Test2 t2
WHERE t2.id = t1.id
ORDER BY Someid DESC) x

id Someid  ID
1   3   1
1   2   1
2   3   2
2   2   2
3   3   3
3   2   3
4   NULL    NULL
5   NULL    NULL

As you can see, you got NULLS back for the rows with id 4 and 5, this is because table Test2 does not have those ids.

Now let’s put that derived table in a user defined function

CREATE FUNCTION fnTopTwoCustomers(@id int)
RETURNS TABLE
AS
RETURN
SELECT TOP(2) Someid,ID FROM Test2 t2
WHERE t2.id = @id
ORDER BY Someid DESC

A quick test with id 1 passed in

SELECT * FROM dbo.fnTopTwoCustomers(1)

Someid ID
3   1
2   1

Now let’s rewrite the queries from before to use this function instead of the derived table

SELECT * FROM Test1 t1
CROSS APPLY dbo.fnTopTwoCustomers(t1.id) x

SELECT * FROM Test1 t1
OUTER APPLY dbo.fnTopTwoCustomers(t1.id) x

The output is the same as before but the code looks much cleaner and easier to maintain

That is all for today, come back tomorrow for the next post in this series

SQL Advent 2011 Day 16: CROSS APPLY and OUTER APPLY

About SQLDenis