SQL server:apply table operator

SQL Server 2005 (including) The above version, add the Apply table operation, for our daily query brings great convenience.

　　The new apply table operator applies the right-table expression to each row in the left-hand table expression. It is not possible to calculate the table expression first as join, and apply must logically evaluate the left expression first. The logical order of this calculation input allows the right expression to be associated to the left table expression.

There are two forms of apply, one is outer apply, the other is cross apply, and 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 cross is specified, The result set does not contain rows in the left table expression that causes the right table expression to be empty.

It would be clearer to explain this in a few examples.

Example 1:cross APPLY form

For example: The data stored in a column in a largetable table is data separated by the ":" Number, and when we process it, we may want to separate the value first, then put each separated value in a single row and then do the table.

Raw data (largetable table):
　　

For the sake of simplicity, let's take one of the id=2 's lines, these data separated by the numbers, which may be the primary key (T1) of one of our tables, we may need to put these values in a temporary table, and associate with the T1 table to do some processing.

The result of processing this delimited data is as follows:

　　

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

This is only done with one line of processing, if we use the 3 rows to do so, the three lines with: number separated values are placed in a table, how to deal with it?

The protagonist of today should be the debut. The above operation can be handled by using the Apply Table operator line statement.

　　

SELECT a FROM dbo. Largetable as LT--Actual table
Cross APPLY Dbo.split (LT. Name, ': ')--a custom table-valued function that processes data separated by a character, returning the data to a table
WHERE a <> '--Remove the empty data from the a field in the result table

The results of processing are as follows:

　　

is not very simple.

The additional definition is the custom table-valued function (split), which I'm looking for online, similar. NET in the split operation, the code is 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) [email protected]
Set @i = CharIndex (@Splits, @Sql)
End

If @Sql <> '
Insert @temp Values (@Sql)
Return
End

Example 2:outer APPLY form

Scenario: There is a supplier table (Supplier) and a supplier's product list (products), we want to take the highest price per supplier of two products.

Supplier Table:

　　

Supplier Product List:

　　

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

IF object_id (' dbo.fn_top_products ') is not NULL
DROP FUNCTION dbo.fn_top_products;
GO
--two items with the highest unit price 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

OK, the pre-data are ready, let us try outer apply form to query, what results will appear.

Execute the following statement:

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 the cross apply form is used, the following query is executed:

　　

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:

　　　　

You see the difference between outer apply and cross apply.

　　Once again, the execution of apply, which logically computes the left table expression (above the Largetable table), then applies the right expression (split of the above custom table-valued function) to each row of the left-hand table expression. The actual is to pass the column reference of the outer query as a parameter to the table-valued function.

SQL server:apply table operator