Where to use Outer Apply

Question

MASTER TABLE

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

DETAILS 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

I am getting the same results when LEFT JOIN and OUTER APPLY is used.

LEFT JOIN

SELECT T1.ID,T1.NAME,T2.PERIOD,T2.QTY 
FROM MASTER T1
LEFT JOIN DETAILS T2 ON T1.ID=T2.ID

OUTER APPLY

SELECT T1.ID,T1.NAME,TAB.PERIOD,TAB.QTY 
FROM MASTER T1
OUTER APPLY
(
   SELECT ID,PERIOD,QTY 
   FROM DETAILS T2
   WHERE T1.ID=T2.ID
)TAB

Where should I use LEFT JOIN AND where should I use OUTER APPLY

Iglesias

Similar to this useful Q&A #1139660. – Subcutaneous

I think this question has more real time examples #9275632. Check Martin's answer. One of my favorite answer in SO – Aggravation

Answer 1

A LEFT JOIN should be replaced with OUTER APPLY in the following situations.

1. If we want to join two tables based on TOP n results

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

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 bring wrong results ie, it will bring only latest two dates data from Details table irrespective of Id even though we join with Id. So the proper solution is using 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

Here is the working : In LEFT JOIN , TOP 2 dates will be joined to the MASTER only after executing the query inside derived table D. In OUTER APPLY, it uses joining WHERE M.ID=D.ID inside the OUTER APPLY, so that each ID in Master will be joined with TOP 2 dates which will bring 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     | 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 functionality using functions.

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

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

And the function goes 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

3. Retain NULL values when unpivoting

Consider you have the below table

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 one column, it will eliminate NULL values by default.

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

which generates the below result. Note that we have missed the record of 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 an APPLY can be used(either CROSS APPLY or OUTER APPLY, which is interchangeable).

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

which forms the following result and retains 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

Iglesias

Asked and answered by the same user, based on an answer in the other referenced question? #1139660 – Chauffer

"OUTER APPLY can be used to retain NULL values when unpivoting" – in a comment to your other answer, I already pointed out that it doesn't matter which APPLY to use if you want to use it for unpivoting, yet you never addressed that issue in your replies. This answer implies (and the other as good as states explicitly) that CROSS APPLY doesn't retain NULLs in such a scenario, which is wrong. You should check your facts before sharing knowledge. – Causative

The question is about OUTER APPLY not about CROSS APPLY. I haven't mentioned CROSS APPLY because I am here to answer WHERE SHOULD I USE OUTER APPLY. I such case, there are other way like using UNION ALL instead of UNPIVOT. Should I add them all in the answer? Does that make sense? @Andriy M – Iglesias

Where CROSS APPLY and OUTER APPLY are interchangeable, it should be mentioned, in my opinion, because otherwise people (especially new learners) might think they could use only OUTER APPLY and not CROSS APPLY in those cases. As for unpivoting in particular, that's where you yourself are mistaken by thinking the two APPLYs behave differently there. Your other answer still states that CROSS APPLY doesn't retain NULLs while this answer says that OUTER APPLY does. So first, fix that discrepancy, and second, consider mentioning that both APPLYs can be used in this scenario, so as to leave no doubt. – Causative

I didn't mention about CROSS APPLY as the question is about OUTER APPLY. Ok. If u think the user may get confused on CROSS APPLY and OUTER APPLY, I will update that in answer. @Andriy M – Iglesias

I am not mistaken on CROSS APPLY and OUTER APPLY as you said. I didn't mention about CROSS APPLY as the question is about OUTER APPLY. Ok. If u think the user may get confused on CROSS APPLY and OUTER APPLY, I will update that in answer. @Andriy M – Iglesias

nice explanation – Highbrow

Answer 2

In your example queries the results are indeed the same.

But OUTER APPLY can do more: For each outer row you can produce an arbitrary inner result set. For example you can join the TOP 1 ORDER BY ... row. A LEFT JOIN can't do that.

The computation of the inner result set can reference outer columns (like your example did).

OUTER APPLY is strictly more powerful than LEFT JOIN. This is easy to see because each LEFT JOIN can be rewritten to an OUTER APPLY just like you did. It's syntax is more verbose, though.

Sells