Detailed application of cross apply and outer apply in SQL Server

In SQL Server database operations, a new apply table operator has been added for more than 2005 versions. 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.

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.

1. SELECT a FROM dbo. Largetable as lt--actual table
2. Cross APPLY Dbo.split (LT. Name, ': ')--a custom table-valued function that processes data separated by a character, returning the data to a table
3. WHERE a <> "--removes data from the result table that has a field empty

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:

2. SELECT * from Dbo.split (' 581::579::519::279::406::361::560 ', ': ')
4. ALTER Function [dbo]. [Split] (@Sql varchar (8000), @Splits varchar (10))
6. Returns @temp Table (a varchar (100))
8. As
10. Begin
12. Declare @i Int
14. Set @Sql = RTrim (LTrim (@Sql))
16. Set @i = CharIndex (@Splits, @Sql)
18. While @i >= 1
20. Begin
22. Insert @temp Values (left (@Sql, @i-1))
24. Set @Sql = SubString (@Sql, @i+1,len (@Sql) [email protected])
26. Set @i = CharIndex (@Splits, @Sql)
28. End

1. If @Sql <> "
2. Insert @temp Values (@Sql)
3. Return
4. 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. OK, the pre-data are ready, let us try outer apply form to query, what results will appear.

1. IF object_id (' dbo.fn_top_products ') is not NULL
2. DROP FUNCTION dbo.fn_top_products;
3. GO

--two items with the highest unit price according to the supplier ID

1. CREATE FUNCTION dbo.fn_top_products
2. (@supid as INT)
3. RETURNS TABLE
4. As
5. RETURN
6. SELECT TOP (2) Id as Productid,productname,unitprice
7. FROM dbo. Products
8. WHERE SupplierId = @supid
9. ORDER by UnitPrice DESC
10. GO

Execute the following statement:

1. SELECT s.id as Supplierid,s.companyname,unitprice from dbo. Supplier as S
2. 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:

1. SELECT s.id as Supplierid,s.companyname,unitprice from dbo. Supplier as S
2. 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.

The above is an example of an application of the SQL Server database apply table operator, which I hope will help you.

Detailed application of cross apply and outer apply in SQL Server