First look at grammar:

<left_table_expression>  {cross|outer} apply<right_table_expression>

Let's look at the two steps involved in the apply operation:

A1 Apply the right table expression (<right_table_expression>) to the row entered in the left table (<left_table_expression>)

A2: Add external rows;

apply is like calculating the left input first and then the right input for each row in the left input. (This sentence is very important and may not be understood, but remember first that it will be explained in detail later.)

Finally, the difference between cross-apply and outer-apply is illustrated by combining the above two steps.

Cross application and outer application always contain step A1, and only outer application contains step A2. If the right table expression is applied to the left row of cross application, the space will not be returned to that row. And outer apply returnsthis Row, and the right table expression of the row has a null attribute.

Seeing the above explanations or steps, you may still be confused.

The following is illustrated by an example:

--Table 1 ([dbo].[Customers]  Field description: customerid--Consumer id， city -- City:
CREATE TABLE [dbo].[Customers](
    [customerid][char](5)NOTNULL,
    [city][varchar](10)NOTNULL,
PRIMARY KEY CLUSTERED ( [customerid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]
) ON [PRIMARY]
--Insert data into table 1:
insert into dbo.Customersvalues('FISSA','Madrid');
insert into dbo.Customersvalues('FRNDO','Madrid');
insert into dbo.Customersvalues('KRLOS','Madrid');
insert into dbo.Customersvalues('MRPHS','Zion');
--Table two ([dbo].[Orders]  Field description: orderid--Order id ，customerid -- Consumer id): 
CREATE TABLE [dbo].[Orders](
    [orderid] [int]NOT NULL,
    [customerid][char](5)NULL,
PRIMARY KEY CLUSTERED ([orderid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]
) ON [PRIMARY]
--Insert data into table 2:
insert into dbo.Ordersvalues(1,'FRNDO');
insert into dbo.Ordersvalues(2,'FRNDO');
insert into dbo.Ordersvalues(3,'KRLOS');
insert into dbo.Ordersvalues(4,'KRLOS');
insert into dbo.Ordersvalues(5,'KRLOS');
insert into dbo.Ordersvalues(6,'MRPHS');
insert into dbo.Ordersvalues(7,null);
--Query the inserted data:
select *from dbo.Customers
select *from dbo.orders

Result:
customerid city
---------- ----------
FISSA      Madrid
FRNDO      Madrid
KRLOS      Madrid
MRPHS      Zion
 
(4 Row affected)
 
orderid     customerid
----------- ----------
1           FRNDO
2           FRNDO
3           KRLOS
4           KRLOS
5           KRLOS
6           MRPHS
7           NULL
 
(7 Row affected)

[Example] Get the latest two orders for each consumer:

1. Use cross apply

SELECT  *
FROM    dbo.CustomersAS A
        CROSS APPLY( SELECT TOP 2 *
                     FROM      dbo.OrdersAS B
                     WHERE     A.customerid= B.customerid
                     ORDER BY  orderid DESC
                    ) AS AB;

customerid city       orderid     customerid
---------- ---------- ---------------------
FRNDO      Madrid    2           FRNDO
FRNDO      Madrid    1           FRNDO
KRLOS      Madrid    5           KRLOS
KRLOS      Madrid    4           KRLOS
MRPHS     Zion       6           MRPHS

Process analysis:

It first obtains the data in the left table [dbo.Customers], then puts the data one by one into the right table form, respectively obtains the result set, finally integrates the result set into the final return result set (T1 data enters into T2 one by one like for loop, then returns to a set), and finally integrates all the sets into one. Finally, let's take a look at the words mentioned above (using apply is like calculating the left input first, and then the right input for each line in the left input).

2. Use outer apply

SELECT  *
FROM    dbo.CustomersAS A
        OUTER APPLY( SELECT TOP 2
                                *
                     FROM      dbo.OrdersAS B
                     WHERE     A.customerid= B.customerid
                     ORDER BY  orderid DESC
                    ) AS AB;

customerid city       orderid     customerid
---------- ---------- ---------------------
FISSA      Madrid    NULL        NULL
FRNDO      Madrid    2           FRNDO
FRNDO      Madrid    1           FRNDO
KRLOS      Madrid    5           KRLOS
KRLOS      Madrid    4           KRLOS
MRPHS     Zion       6           MRPHS

Result analysis:

It is found that outer apply gets one more line than cross. We combine the differences mentioned above (cross application and outer application always contain step A1, only outer application contains step A2, and if the right table expression is applied to the left row of cross application, the space product is returned, then the line is not returned. And outerapply returns the line change, and the right table expression of the line change has the attribute null).

[example two]

;with tb1(Customer number,Sales date,Sales volume)AS(
    select '001','2017-05-01',460 union all
    select '001','2017-05-02',240 union  all
    select '001','2017-05-03',300 
),TB2(Customer number,Payment amount)AS(
    select  '001',500
)
SELECT  t1.Customer number ,
        t1.Sales date ,
        t1.Sales volume ,
        CASE WHEN t2.Payment amount <= ISNULL(tt1.p_Sales volume, 0) THEN 0
             ELSE CASE WHEN ( ISNULL(p_Sales volume, 0) + t1.Sales volume ) >= t2.Payment amount
                       THEN t2.Payment amount - ISNULL(p_Sales volume, 0)
                       ELSE t1.Sales volume
                  END
        END AS Actual payment
FROM    tb1 AS t1
        CROSS APPLY ( SELECT    SUM(Payment amount) AS Payment amount
                      FROM      TB2
                      WHERE     TB2.Customer number = t1.Customer number
                    ) AS t2
        OUTER APPLY ( SELECT    SUM(Sales volume) AS p_Sales volume
                      FROM      tb1 AS tt
                      WHERE     tt.Customer number = t1.Customer number
                                AND tt.Sales date < t1.Sales date
                    ) AS tt1;

--Result
//Customer Number Sales Date Sales Payment
---- ---------- ----------- -----------
001  2017-05-01 460         460
001  2017-05-02 240         40
001  2017-05-03 300         0