引言：
SQL学习时，发现T-SQL还有Cross Apply和Outer Apply功能，以前从未接触过，查了一些资料：http://www.2cto.com/database/201304/206330.html
然后发现Oracle在12c的新版本上也引入了Cross Apply和Outer Apply，而Cross Apply和Outer Apply可以用inner join和left join改写。
知识点：
apply语法：<left_table_expression> {cross|outer} apply <right_table_expression>
apply运算涉及的两个步骤：
A1:把右表表达式(<right_table_expression>)应用到左表(<left_table_expression>)输入的行；
A2:添加外部行；
使用apply就像是先计算左输入，让后为左输入中的每一行计算一次右输入。
Cross Apply原SQL：
with Customers as
(
select 'FISSA' as customerid,'Madrid' as city union all
select 'FRNDO','Madrid' union all
select 'KRLOS','Madrid' union all
select 'MRPHS','Zion' union all
select 'Choc','Beijing'
),
Orders as
(select 1 as orderid,'FRNDO' as customerid union all
select 2,'FRNDO' union all
select 3,'KRLOS' union all
select 4,'KRLOS' union all
select 5,'KRLOS' union all
select 6,'MRPHS' union all
select 7,null union all
select 8,'KRLOS' union all
select 9,'KRLOS' union all
select 10,'FRNDO'
)
select *
from Customers as C
cross apply
(select top 2 *
from Orders as O
where C.customerid=O.customerid
order by orderid desc) as CA
Cross Apply过程分析：
先得出左表【Customers】里的数据，然后把此数据一条一条的放入右表表式中，分别得出结果集，最后把结果集整合到一起就是最终的返回结果集了（T1的数据像for循环一样 一条一条的进入到T2中 然后返回一个集合，最后把所有的集合整合到一块就是最终的结果）。
最后我们再理解一下：使用apply就像是先计算左输入，然后为左输入中的每一行计算一次右输入。
Cross Apply改写为inner join：
with Customers as
(
select 'FISSA' as customerid,'Madrid' as city union all
select 'FRNDO','Madrid' union all
select 'KRLOS','Madrid' union all
select 'MRPHS','Zion' union all
select 'Choc','Beijing'
),
Orders as
(select 1 as orderid,'FRNDO' as customerid union all
select 2,'FRNDO' union all
select 3,'KRLOS' union all
select 4,'KRLOS' union all
select 5,'KRLOS' union all
select 6,'MRPHS' union all
select 7,null union all
select 8,'KRLOS' union all
select 9,'KRLOS' union all
select 10,'FRNDO'
)
select *
from Customers as C
inner join Orders as O
on O.orderid in (select top 2 orderid
from Orders as O
where C.customerid=O.customerid
order by orderid desc) 
Outer Apply：
with Customers as
(
select 'FISSA' as customerid,'Madrid' as city union all
select 'FRNDO','Madrid' union all
select 'KRLOS','Madrid' union all
select 'MRPHS','Zion' union all
select 'Choc','Beijing'
),
Orders as
(select 1 as orderid,'FRNDO' as customerid union all
select 2,'FRNDO' union all
select 3,'KRLOS' union all
select 4,'KRLOS' union all
select 5,'KRLOS' union all
select 6,'MRPHS' union all
select 7,null union all
select 8,'KRLOS' union all
select 9,'KRLOS' union all
select 10,'FRNDO'
)
select *
from Customers as C
outer apply
(select top 2 *
from Orders as O
where C.customerid=O.customerid
order by orderid desc) as CA
;
Outer Apply过程分析：发现outer apply得到的结果比cross apply多了两行。cross apply和outer apply 总是包含步骤A1，只有outer apply包含步骤A2。如果cross apply左行应用右表表达式时返回空积，则不返回该行。而outer apply返回该行。
Outer Apply改写为left join：
with Customers as
(
select 'FISSA' as customerid,'Madrid' as city union all
select 'FRNDO','Madrid' union all
select 'KRLOS','Madrid' union all
select 'MRPHS','Zion' union all
select 'Choc','Beijing'
),
Orders as
(select 1 as orderid,'FRNDO' as customerid union all
select 2,'FRNDO' union all
select 3,'KRLOS' union all
select 4,'KRLOS' union all
select 5,'KRLOS' union all
select 6,'MRPHS' union all
select 7,null union all
select 8,'KRLOS' union all
select 9,'KRLOS' union all
select 10,'FRNDO'
)
select *
from Customers as C
left join Orders as O
on O.orderid in (select top 2 orderid
from Orders as O
where C.customerid=O.customerid
order by orderid desc)
个人结论：用Cross Apply，Outer Apply的SQL更简洁，查看执行计划，我理解执行效率应该更高些。
