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

Question

I looked at CROSS / OUTER APPLY with a colleague, and we are struggling to find real examples of where to use them.

I spent quite some time on When should I use Cross Apply for Inner Join? and googling, but the main (only) example seems rather strange (using rowcount from a table to determine how many rows to select from another table).

I thought this scenario could benefit from OUTER APPLY :

Contact table (contains 1 entry for each contact) Communication protocol table (may contain n phone, fax, email for each contact)

But using subqueries, common table expressions, OUTER JOIN with RANK() and OUTER APPLY all seem to work the same. I assume this means that the script is not applicable to APPLY .

Share some real life examples and help explain this feature!

Answer 1

Some uses for APPLY are ...

1) The largest number of requests for each group (may be more effective for some capacities)

 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) A function call with a table for each row of an external request

 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) Unable to select multiple column groups

Assumes 1NF violating table structure ....

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

An example of using the syntax 2008+ VALUES .

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

In 2005, you can use UNION ALL .

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

Answer 2

There are various situations where you cannot avoid CROSS APPLY or OUTER APPLY .

You have two tables.

MASTER TABLE

 x------x--------------------x | Id | Name | x------x--------------------x | 1 | A | | 2 | B | | 3 | C | x------x--------------------x 

PARTS TABLE

 x------x--------------------x-------x | Id | PERIOD | QTY | x------x--------------------x-------x | 1 | 2014-01-13 | 10 | | 1 | 2014-01-11 | 15 | | 1 | 2014-01-12 | 20 | | 2 | 2014-01-06 | 30 | | 2 | 2014-01-08 | 40 | x------x--------------------x-------x 

& nbsp CROSS APPLY

There are many situations where we need to replace INNER JOIN with CROSS APPLY .

1. If we want to join 2 tables on TOP n results with INNER JOIN functionality

Consider whether we need to select the Id and Name from the Master and the last two dates for each Id from the Details table .

 SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M INNER JOIN ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D ORDER BY CAST(PERIOD AS DATE)DESC )D ON M.ID=D.ID 

The above query generates the following result.

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | x------x---------x--------------x-------x 

See, he generated the results for the last two dates with the last two Id dates, and then attached these records only to the external query on Id , which is incorrect. For this we need to use CROSS APPLY .

 SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M CROSS APPLY ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D WHERE M.ID=D.ID ORDER BY CAST(PERIOD AS DATE)DESC )D 

and forms its result.

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-08 | 40 | | 2 | B | 2014-01-06 | 30 | x------x---------x--------------x-------x 

Here is the work. A query inside CROSS APPLY can refer to an external table where an INNER JOIN cannot do this (it produces a compilation error). When finding the last two dates, the connection is performed inside CROSS APPLY i.e. WHERE M.ID=D.ID

2. When we need INNER JOIN functions using functions.

CROSS APPLY can be used as a replacement for INNER JOIN when we need to get the result from the Master and function table.

 SELECT M.ID,M.NAME,C.PERIOD,C.QTY FROM MASTER M CROSS APPLY dbo.FnGetQty(M.ID) C 

And here is the function

 CREATE FUNCTION FnGetQty ( @Id INT ) RETURNS TABLE AS RETURN ( SELECT ID,PERIOD,QTY FROM DETAILS WHERE ID=@Id ) 

which generated the following result:

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-11 | 15 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-06 | 30 | | 2 | B | 2014-01-08 | 40 | x------x---------x--------------x-------x 

& nbsp EXTERNAL APPLICATION

1. If we want to join 2 tables in TOP n using LEFT JOIN functionality

Consider whether we need to select Id and Name from Master and the last two dates for each Id table from Details .

 SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M LEFT JOIN ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D ORDER BY CAST(PERIOD AS DATE)DESC )D ON M.ID=D.ID 

which forms the following result:

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | NULL | NULL | | 3 | C | NULL | NULL | x------x---------x--------------x-------x 

This will lead to incorrect results, i.e. only the last two date data from the Details table will appear, regardless of Id , although we join Id . Therefore, the correct solution uses OUTER APPLY .

 SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M OUTER APPLY ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D WHERE M.ID=D.ID ORDER BY CAST(PERIOD AS DATE)DESC )D 

which forms the next desired result

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-08 | 40 | | 2 | B | 2014-01-06 | 30 | | 3 | C | NULL | NULL | x------x---------x--------------x-------x 

2. When we need LEFT JOIN functions using functions .

OUTER APPLY can be used as a substitute for LEFT JOIN when we need to get the result from the Master and function table.

 SELECT M.ID,M.NAME,C.PERIOD,C.QTY FROM MASTER M OUTER APPLY dbo.FnGetQty(M.ID) C 

And the function is here.

 CREATE FUNCTION FnGetQty ( @Id INT ) RETURNS TABLE AS RETURN ( SELECT ID,PERIOD,QTY FROM DETAILS WHERE ID=@Id ) 

which generated the following result:

 x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-11 | 15 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-06 | 30 | | 2 | B | 2014-01-08 | 40 | | 3 | C | NULL | NULL | x------x---------x--------------x-------x 

& nbsp General feature of CROSS APPLY and OUTER APPLY

CROSS APPLY or OUTER APPLY can be used to store NULL values ​​when they cannot be used interchangeably.

You have the table below

 x------x-------------x--------------x | Id | FROMDATE | TODATE | x------x-------------x--------------x | 1 | 2014-01-11 | 2014-01-13 | | 1 | 2014-02-23 | 2014-02-27 | | 2 | 2014-05-06 | 2014-05-30 | | 3 | NULL | NULL | x------x-------------x--------------x 

When you use UNPIVOT to bring FROMDATE AND TODATE to a single column, NULL values ​​will be deleted by default.

 SELECT ID,DATES FROM MYTABLE UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P 

which generates the result below. Please note that we skipped the entry Id number 3

  x------x-------------x | Id | DATES | x------x-------------x | 1 | 2014-01-11 | | 1 | 2014-01-13 | | 1 | 2014-02-23 | | 1 | 2014-02-27 | | 2 | 2014-05-06 | | 2 | 2014-05-30 | x------x-------------x 

In such cases, it is useful to use CROSS APPLY or OUTER APPLY

 SELECT DISTINCT ID,DATES FROM MYTABLE OUTER APPLY(VALUES (FROMDATE),(TODATE)) COLUMNNAMES(DATES) 

which forms the following result and stores Id , where its value is 3

  x------x-------------x | Id | DATES | x------x-------------x | 1 | 2014-01-11 | | 1 | 2014-01-13 | | 1 | 2014-02-23 | | 1 | 2014-02-27 | | 2 | 2014-05-06 | | 2 | 2014-05-30 | | 3 | NULL | x------x-------------x 

Answer 3

One example of real life would be if you had a scheduler and you wanted to find out what the most recent journal entry was for each scheduled task.

 select t.taskName, lg.logResult, lg.lastUpdateDate from task t cross apply (select top 1 taskID, logResult, lastUpdateDate from taskLog l where l.taskID = t.taskID order by lastUpdateDate desc) lg 

Answer 4

To answer the above example, follow these steps:

 create table #task (taskID int identity primary key not null, taskName varchar(50) not null) create table #log (taskID int not null, reportDate datetime not null, result varchar(50) not null, primary key(reportDate, taskId)) insert #task select 'Task 1' insert #task select 'Task 2' insert #task select 'Task 3' insert #task select 'Task 4' insert #task select 'Task 5' insert #task select 'Task 6' insert #log select taskID, 39951 + number, 'Result text...' from #task cross join ( select top 1000 row_number() over (order by a.id) as number from syscolumns a cross join syscolumns b cross join syscolumns c) n 

And now run two queries with an execution plan.

 select t.taskID, t.taskName, lg.reportDate, lg.result from #task t left join (select taskID, reportDate, result, rank() over (partition by taskID order by reportDate desc) rnk from #log) lg on lg.taskID = t.taskID and lg.rnk = 1 select t.taskID, t.taskName, lg.reportDate, lg.result from #task t outer apply ( select top 1 l.* from #log l where l.taskID = t.taskID order by reportDate desc) lg 

You can see that the external query is being applied more efficiently. (Failed to attach plan because I am a new user ... Doh.)