The two forms of SQL about apply cross apply and outer apply

Reprint: http://www.cnblogs.com/Leo_wl/archive/2013/04/02/2997012.html

There are two forms of apply: 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:

1. A1: Applies the right table expression (<right_table_expression>) to the row of the left table (<left_table_expression>) input;
2. A2: add external rows;

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 very important, may not understand, but first remember, there will be a detailed explanation later )

Finally, combine the two steps above 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, and 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 a row, and the property of the right table expression for the row is null.

See above explanations or steps everyone may still be a fog, unintelligible. Here is an example to illustrate:

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 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 ');

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_as null,primary KEY CLUSTERED (    [ OrderID] ASC) with (Ignore_dup_key = OFF) on [PRIMARY]) on [PRIMARY]

Insert data into table two:

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

Results

Example: topic: Get the latest two orders per consumer:

With 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

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: Try to see the results with outer apply:

SQL statements:

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

Results

Results Analysis:

Finding that outer apply results in a row more than cross, we combine the differences written above (crossapply 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 a row, and the property of the right table expression for the row is null).

The two forms of SQL about apply cross apply and outer apply