SQL Server: APPLY table Operator

In SQL Server 2005 or later versions, the APPLY table operation is added, which greatly facilitates our daily queries.

　　The new APPLY table operator applies the right table expression to each row in the left table expression. Unlike JOIN, it can calculate the table expression first. APPLY must calculate the left expression logically first. The logic order of input calculation allows the right expression to be associated with the left table expression.

APPLY has two forms: outer apply and cross apply. The difference is that OUTER is specified, which means that the result set contains rows in the left table expression that makes the right table expression empty, if CROSS is specified, the result set does not contain rows in the left table expression that makes the right table expression null.

It will be clearer to use several examples.

Example 1: CROSS APPLY

For example, the data stored in a column in the LargeTable table is separated by the ":" number. during processing, we may need to separate the values first, then, separate each value in one table and process the table.

Raw data (LargeTable table ):
　　

For simplicity, we first take a row with id = 2 for processing. The data separated by a number may be the primary key (t1) of a table ), we may need to put these values into a temporary table and associate them with table t1 for some processing.

The result of processing the separated data is as follows:

　　

If you use a previous version to process this operation, it should be very complicated and you did not expect how to handle it for the time being. If someone has implemented this operation, please remind me.

This is only the processing of one row. If we use three rows to do this, we will put the values of the three rows separated by a number in a table. What should we do?

Today's "APPLY" is coming soon. You can use the APPLY table operator to process the preceding operations in a row.

　　

SELECT a FROM dbo. LargeTable as lt -- actual table
Cross apply dbo. split (LT. Name, ':') -- a user-defined Table value function that processes data separated by a certain character. In this way, a table is returned.
WHERE a <> ''-- remove data with null field a in the result table.

The processing result is as follows:

　　

Is it easy.

The custom table value function (split) needs to be defined. This is what I found on the Internet, similar to the Split operation in. Net. The Code is as follows:

　　

/*
Usage: SELECT * FROM dbo. split ('2017: 581: 579: 519: 279: 406: 361 ',':')
*/
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

Scenario: There is a Supplier table (Supplier) and a Supplier product table (Products). We need to take the two Products with the highest unit price among each Supplier.

Supplier table:

　　

Supplier product table:

　　

First, we create a UDF (dbo. fn_top_products), which returns the two items with the highest unit price based on the supplier ID.

IF OBJECT_ID ('dbo. fn_top_products') IS NOT NULL
Drop function dbo. fn_top_products;
GO
-- Obtain the two items with the highest unit price based on 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, we have prepared all the data in the early stage. Let's try to use outer apply to query the results.

Run 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 execution result is as follows:

　　　

Note that the last record is NULL. reed's unit price is NULL because there is no product.

If cross apply is used, run the following query:

　　

Select s. id AS SupplierId, S. CompanyName, UnitPrice FROM dbo. Supplier AS S
Cross apply dbo. fn_top_products (S. id) AS P

The output result is as follows:

　　　　

 

We can see the difference between outer apply and cross apply.

 

　　Let's talk about the execution process of APPLY again. It first logically calculates the left table expression (the above LargeTable table), and then splits the right expression (the above custom table value function is Split) apply to each row of the expression in the left table. Actually, the column reference of the external query is passed to the table value function as a parameter.