Real life example, when to use OUTER / CROSS APPLY in SQL

Question

I have been looking at CROSS / OUTER APPLY with a colleague and we're struggling to find real life examples of where to use them.

I've spent quite a lot of time looking at When should I use CROSS APPLY over INNER JOIN? and googling but the main (only) example seems pretty bizarre (using the rowcount from a table to determine how many rows to select from another table).

I thought this scenario may benefit from OUTER APPLY:

Contacts Table (contains 1 record for each contact) Communication Entries Table (can contain a phone, fax, email for each contact)

But using subqueries, common table expressions, OUTER JOIN with RANK() and OUTER APPLY all seem to perform equally. I'm guessing this means the scenario isn't applicable to APPLY.

Please share some real life examples and help explain the feature!

Answer 1

Some uses for APPLY are...

1) Top N per group queries (can be more efficient for some cardinalities)

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name 

2) Calling a Table Valued Function for each row in the outer query

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

3) Reusing a column alias

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)  
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)  

4) Unpivoting more than one group of columns

Assumes 1NF violating table structure....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,
     Foo1 INT, Bar1 INT,
     Foo2 INT, Bar2 INT,
     Foo3 INT, Bar3 INT
  );

Example using 2008+ VALUES syntax.

SELECT Id,
       Foo,
       Bar,
       GrpName
FROM   T
       CROSS APPLY (VALUES('1', Foo1, Bar1),
                          ('2', Foo2, Bar2),
                          ('3', Foo3, Bar3)) V(GrpName, Foo, Bar); 

In 2005 UNION ALL can be used instead.

SELECT Id,
       Foo,
       Bar,
       GrpName
FROM   T
       CROSS APPLY (SELECT '1', Foo1, Bar1 
                    UNION ALL
                    SELECT '2', Foo2, Bar2 
                    UNION ALL
                    SELECT '3', Foo3, Bar3) V(GrpName, Foo, Bar);