SQL Server Apply table operator use introduction _mssql2005

The new Apply table operator applies the right table expression to each row in the left table expression. It does not compute that table expression as a join, and the apply must first compute the left expression logically. The logical order of this calculation input allows the right expression to be associated with the left-table expression.

Apply has two forms, one is outer apply, one is cross apply, the difference is that specifying outer means that the result set will contain rows in the left table expression that make the right table expression empty, and specify cross, instead, The result set does not contain rows in the left table expression that makes the right table expression empty.

Explain this more clearly in a few examples.

Example 1:CROSS APPLY Form

For example, a column in a largetable table stores data that is separated by a ":" Number, and when we do this, we may want to separate the value first, then separate each value in a single row, and then handle the table.

Raw data (largetable table):
　　

In order to be simple, we first take one of the id=2, which is separated by the data, may be the primary key (T1) of one of our tables, we may need to put these values in a temporary table, and T1 Table Association, do some processing.

The results of processing this delimited data are shown below:

　　

If you use the previous version to handle this operation, it should be very hair miscellaneous, temporarily did not think how to deal with, if someone has achieved, you can hint.

This is only done in one line of processing, if we use the 3 rows of the above figure to do this, the three lines separated by the number of values in a table, how to deal with it?

Today's protagonist apply should be on the debut. You can handle the above operation by using the Apply Table operator line statement.

Copy Code code as follows:

SELECT a FROM dbo. Largetable as LT--Actual table
CROSS APPLY dbo.split (LT. Name, ': '--custom table-valued functions, processing data separated by a character, and returning the data to a table
WHERE a <> '--remove data from a field in the result table that is blank

The results of the processing are shown below:

　　

is not very simple.
What needs to be added is the custom table-valued function (split), which I looked for online, similar. NET split operation, the code is as follows:

Copy Code code as follows:

/*
How to use: SELECT * from Dbo.split (' 581::579::519::279::406::361::560 ', ': ')
*/
ALTER Function [dbo]. [Split] (@Sql varchar (8000), @Splits varchar (10))
Returns @temp Table (a varchar (100))
As
Begin
Declare @i Int
Set @Sql = RTrim (LTrim (@Sql))
Set @i = CharIndex (@Splits, @Sql)
While @i >= 1
Begin
Insert @temp Values (left (@Sql, @i-1))
Set @Sql = SubString (@Sql, @i+1,len (@Sql)-@i)
Set @i = CharIndex (@Splits, @Sql)
End
If @Sql <> '
Insert @temp Values (@Sql)
Return
End

Example 2: OUTER APPLY Form

　　Scene: There is a supplier table (Supplier) and Supplier Product List (products), we want to take each supplier of the highest unit price of two products.

Supplier list:

　　

　　Supplier Product List:

　　

　　First, we create a custom table-valued function (dbo.fn_top_products) that returns the highest unit price of two items based on the supplier ID.

Copy Code code as follows:

IF object_id (' dbo.fn_top_products ') is not NULL
DROP FUNCTION dbo.fn_top_products;
Go
--Obtain the highest unit price of two items according to the Supplier ID
CREATE FUNCTION dbo.fn_top_products
(@supid as INT)
RETURNS TABLE
As
Return
SELECT Top (2) Id as Productid,productname,unitprice
FROM dbo. Products
WHERE SupplierId = @supid
ORDER BY UnitPrice DESC
Go

Well, the previous data are ready, let's try outer apply form to query, what will happen.
Execute the following statement:

Copy Code code as follows:

SELECT s.id as Supplierid,s.companyname,unitprice from dbo. Supplier as S
OUTER APPLY dbo.fn_top_products (s.id) as P

The results of the implementation are as follows:

　　　

Note that the last null record, Reed company because there is no product, so the unit price is null.

If you use the cross apply form, execute the following query:

Copy Code code as follows:

SELECT s.id as Supplierid,s.companyname,unitprice from dbo. Supplier as S
CROSS APPLY dbo.fn_top_products (s.id) as P

The resulting output is as follows:

　　　　

We see the difference between outer apply and cross apply.

　　Again, the execution of apply, which first logically computes the left table expression (the largetable table above), and then applies the right expression (the above custom table-valued function split) to each row of the left table expression. It is actually passing the column reference of the outer query as a parameter to the table-valued function.