SQL two forms of apply cross apply and outer apply (reprint)

The two forms of SQL about apply cross apply and outer apply apply in two forms: cross apply and outer apply first look at syntax: <left_table_expression> {CRO Ss|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_table_expression>) The line applied to the left table (<left_table_expression>) input; A2: adding an outer row; Using apply is like calculating the left input first, and then calculating the right input once for each row in the left input. (This sentence is important, may not understand, but to remember, there will be a detailed explanation later) Finally, combined with the above two steps to illustrate the difference between cross apply and outer apply: cross apply and outer apply always contain step A1, only outer Apply contains step A2, if the cross apply left row returns an empty product when the right table expression is applied, the row is not returned. Instead, outer apply returns the row, and the property of the right table expression for that row is null. See above explanations or steps everyone may still be a fog, unintelligible. Here's an example: first build table One ([dbo]. [Customers] Field Description: CustomerID--Consumer ID, City--Location:

CREATE TABLE [dbo].[Customers](    [CustomerID] [Char](5) COLLATE Chinese_prc_ci_as not NULL,    [ City] [varchar](Ten) COLLATE Chinese_prc_ci_as not NULL,PRIMARY KEY CLUSTERED (    [CustomerID] ASC) with(Ignore_dup_key= OFF) on [PRIMARY])  on [PRIMARY]

To insert data into table one:

Insert  intoDbo. CustomersValues('Fissa','Madrid');Insert  intoDbo. CustomersValues('Frndo','Madrid');Insert  intoDbo. CustomersValues('Krlos','Madrid');Insert  intoDbo. CustomersValues('mrphs','Zion');

To query the inserted data:

Select *  from dbo. Customers

Results

Re-build Table II ([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_asNULL,PRIMARY KEY CLUSTERED (    [OrderID] ASC) with(Ignore_dup_key= OFF) on [PRIMARY])  on [PRIMARY]

Insert data into table two:

Insert  intoDbo. OrdersValues(1,'Frndo');Insert  intoDbo. OrdersValues(2,'Frndo');Insert  intoDbo. OrdersValues(3,'Krlos');Insert  intoDbo. OrdersValues(4,'Krlos');Insert  intoDbo. OrdersValues(5,'Krlos');Insert  intoDbo. OrdersValues(6,'mrphs');Insert  intoDbo. OrdersValues(7,NULL);

Query the inserted data:

Select *  from Dbo.orders

Results

Example: Title: Get the latest two orders for each consumer: cross apply

Select *  from  as    Cross apply    (Selecttop2*from      as   O     where c.customerid=o.customerid      Order  by desc  as Ca

Results

Process Analysis: It is first derived from the left table "dbo."  Customers "In the data, and then put this data one bar into the right table, respectively, the result set, and finally the result set together is the final return result set (T1 of the data like a for loop one entry into the T2 and then return a collection Finally, all the collection into a piece is the end result), and finally we understand the above to remember the words (using apply is like the first to calculate the left input, so that the left input for each row to calculate the right input) is not clear. Experiment: with outer apply try to see the result: SQL statement:

Select *  from  as  outer  apply    (Selecttop2*from      as   O     where c.customerid=o.customerid      Order  by desc  as Ca

Results

Results analysis: found that the results of outer apply more than cross a line, we combine the differences written above (cross apply and outer apply always contain step A1, only outer apply contains step A2, if cross Apply the left row to return an empty product when the right table expression is applied, the row is not returned. and outer apply returns the row, and the property of the right table expression for that row is null, you'll know.

SQL two forms of apply cross apply and outer apply (reprint)