T-SQL Part VIII: CROSS APPLY, OUTER APPLY

除了CROSS JOIN， INNER JOIN， OUTER JOIN之外，T-SQL还提供了CROSS APPLY和OUTER APPLY这两个较为另类的Set操作符。

首先来看CROSS APPLY。跟CROSS JOIN一样，MSDN只在FROM Clause的文档中做了一个介绍，如下：

Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.

The APPLY operator works in the following way to produce the table source for the FROM clause:
Evaluates right_table_source against each row of the left_table_source to produce rowsets.
The values in the right_table_source depend on left_table_source. right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.
Combines the result sets that are produced for each row in the evaluation of right_table_source with the left_table_source by performing a UNION ALL operation.

The list of columns produced by the result of the APPLY operator is the set of columns from the left_table_source that is combined with the list of columns from the right_table_source.

简单来说就是，APPLY操作符的过程就是：

1. 计算左表表达式
2. 将左表表达式结果作为右表输入

通常，对右表表达式，即可以是表，也可以为Function。

例一，右表表达式为Function，左表中名为Tags的Column保存了多个Key拼接而成的字符串。如Tags为“abc, def, acd”

-- Define functionsif object_id('parsetags','TF') is not nulldrop function parsetags;GOcreate function parsetags(    @tags nvarchar(1000),    @splits varchar(10) )returns @t_tags TABLE (tag nvarchar(100))asbegin  set @tags = RTrim(LTrim(@tags))   set @i = CharIndex(@splits,@tags)   while @i >= 1   begin     insert @t_tags Values(Left(@tags,@i-1))     set @tags = SubString(@tags,@i+1,Len(@tags)-@i)     set @i = CharIndex(@Splits,@tags)   end   if @tags <> ''   insert @t_tags Values (@tags)   return;endGO-- Define table t_blogCREATE TABLE t_blog(     blogid INT NOT NULL,     blogcontent NVARCHAR(MAX) NOT NULL,    tags NVARCHAR(200) NOT NULL) GO -- Example of CROSS APPLYSELECT * FROM t_blog CROSS APPLY parsetags(t_blog.tags, ';')GO

例二，右表表达式为另外一张表，选出每个Customer最大的两笔Sales订单。

-- Create Customer tableCREATE TABLE t_customer(     [id] NVARCHAR(50) NOT NULL,     [name] NVARCHAR(50) NOT NULL) GO -- Create Sales tableCREATE TABLE t_sales(     [id] NVARCHAR(50) NOT NULL,     [custid] NVARCHAR(50) NOT NULL,    [amount] MONEY NOT NULL) GO-- Using cross applySELECT t_customer.[id] as custid, t_customer.[name] as custname, sales.[id] as saleid, sales.[amount] as salesamount FROM t_customerCROSS APPLY (SELECT top 2 *     FROM t_sales    WHERE t_customer.id = t_sales.custid    ORDER BY amount) AS salesGO

以上都是使用CROSS APPLY做例子，而OUTER APPLY与其的主要区别是，OUTER APPLY会左表表达式中存在而右表表达式运算结果为NULL的项目，跟INNER JOIN跟OUTER JOIN的概念完全一致。

用例二作为说明:

• 使用CROSS APPLY时，没有任何Sales的Customer在结果集中不显示；
• 用OUTER APPLY时，没有Sales的Customer也会在结果集中出现，但其对应的saleid和saleamount都为NULL

是为之记。
Alva Chien
2016.6.14