SQL cross apply and outer apply
Apply has two forms: Cross apply and outer apply.
First look at the Syntax:
<Left_table_expression> {cross | outer} apply <right_table_expression>
Let's take a look at the two steps involved in the apply operation:
- A1: Apply the right table expression (<right_table_expression>) to the input row in the left table (<left_table_expression>;
- 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:
Create Table 1 first ([DBO]. [MERs] field Description: customerid -- consumer ID, city -- City ):
CREATE TABLE [dbo].[Customers]( [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL, [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,PRIMARY KEY CLUSTERED ( [customerid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Insert data to table 1:
insert into dbo.Customers values('FISSA','Madrid');insert into dbo.Customers values('FRNDO','Madrid');insert into dbo.Customers values('KRLOS','Madrid');insert into dbo.Customers values('MRPHS','Zion');
Query the inserted data:
select * from dbo.Customers
Result
Create another table 2 ([DBO]. [orders] field Description: orderid -- order ID, customerid -- consumer ID ):
CREATE TABLE [dbo].[Orders]( [orderid] [int] NOT NULL, [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,PRIMARY KEY CLUSTERED ( [orderid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Insert data to table 2:
insert into dbo.Orders values(1,'FRNDO');insert into dbo.Orders values(2,'FRNDO');insert into dbo.Orders values(3,'KRLOS');insert into dbo.Orders values(4,'KRLOS');insert into dbo.Orders values(5,'KRLOS');insert into dbo.Orders values(6,'MRPHS');insert into dbo.Orders values(7,null);
Query the inserted data:
select * from dbo.orders
Result
Example: Question: Get the latest two orders for each consumer:
Use cross apply
SQL:
select *from dbo.Customers as C cross apply (select top 2 * from dbo.Orders as O where C.customerid=O.customerid order 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 ), 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.) Is it clear.
Experiment: Try outer apply to see the result:
SQL statement:
select *from dbo.Customers as C outer apply (select top 2 * from dbo.Orders as O where C.customerid=O.customerid order by orderid desc) as CA
Result
Result Analysis:
We can see that outer apply has a row more than cross (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..