SQL has two forms of apply: crossapply and outerapplyapply. first look at the Syntax: left_table_expression {cross | outer} applyright_table_expression. Then let's take a look at the two steps involved in the apply operation: a1: right table expression (right _
SQL has two forms of cross apply and outer apply: cross apply and outer apply. Check the syntax first: left_table_expression {cross | outer} apply right_table_expression let's take a look at the two steps involved in the apply operation: A1: Put the right table expression (right _
SQL cross apply and outer apply
Apply has two forms: cross apply and outer apply.
First look at the Syntax:
{Cross | outer} apply
Let's take a look at the two steps involved in the apply operation:
A1: Right table expression ( ) Apply to the left table ( ) Input rows;
A2: Add external rows;
Using apply is like calculating the left input first, and then calculating the right input for each row in the left input. (This sentence is very important and may not be understood, but remember it first. A detailed description will be provided later)
Finally, the difference between cross apply and outer apply is described based on the above two steps:
Cross apply and outer apply always contain step A1. Only outer apply contains step A2. If cross apply applies to the Left row and returns an empty product when the expression in the right table is applied, this row is not returned. Outer apply returns the modified row, and the attribute of the right table expression of the modified row is null.
We can see that the above explanation or steps may still be confusing, and yunyun. The following is an example:
, City -- city ):
. (5) COLLATE Chinese_PRC_CI_AS not null, (10) COLLATE Chinese_PRC_CI_AS not null ,())
Insert data to table 1:
,);,);,);,);
Query the inserted data:
Dbo. MERs
Result
Create another table 2 ([dbo]. [Orders] field Description: orderid -- order id, customerid -- consumer id ):
. (, (5) COLLATE Chinese_PRC_CI_AS NULL ,())
Insert data to table 2:
); Insert into dbo. Orders values (7, null );
Query the inserted data:
Dbo. orders
Result
Example: Question: Get the latest two orders for each consumer:
Use cross apply
SQL:
Dbo. Customers as C cross apply (dbo. Orders as Owhere C. customerid = O. customeridorder by orderid desc) as CA
Result
Process Analysis:
It first obtains the left table [dbo. customers data, and then put the data one by one into the right table, respectively, to obtain the result set, finally, the result set is integrated into the final returned result set (T1 data enters the T2 one by one like a for loop, and then returns a set, and finally integrates all the sets one piece is the final result ), website space. Finally, let's take a look at the words mentioned above (using apply is like calculating the left input first, and then calculating the right input for each row in the left input.
Experiment: Try outer apply to see the result:
SQL statement:
Dbo. Customers as C outer apply (dbo. Orders as Owhere C. customerid = O. customeridorder by orderid desc) as CA
Result
Result Analysis:
We found that outer apply has a row more than cross. Based on the difference described above (cross apply and outer apply always include step A1, virtual host, and only outer apply contains step A2, if cross apply returns an empty product when the expression in the right table is applied to the Left row, this row is not returned. Outer apply returns the modified row, and the attribute of the right table expression of the modified row is null.
, Hong Kong VM