[MSSQL]cross join与cross apply的区别与应用

What is Cross Join in SQL?
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

SyntaxSELECT * FROM table1 CROSS JOIN table2;

Pictorial Presentation of Cross Join syntax

cross join是笛卡尔集的，通常没有join条件，通常是2个集合的关联(表、视图之间)，如下：
select * from sys.objects
cross join sys.tables;

What is Cross Apply in SQL?
The APPLY operator is similar to the JOIN operator, but the difference is that the right-hand side operator of APPLY can reference columns from the left-hand side. Here are two very quick examples:

SELECT a.col, b.resultcolFROM     dbo.tbl aCROSS    APPLY dbo.mytblfunc(a.somecol) AS bThat is, you call a table-valued function where the parameters comes from the table.SELECT C.CustomerName, O.*FROM     Customers COUTER    APPLY (SELECT TOP 1 *                            FROM     Orders O                            WHERE    C.CustomerID = O.CustomerID                            ORDER    BY O.OrderDate DESC, O.OrderID DESC) AS O

In this example we list all customers and the most recent order.

As you in the examples there are both CROSS APPLY and OUTER APPLY. The difference between these two is what happens when the right-hand side returns no rows. With OUTER APPLY, the row from the left-hand side is retained, showing NULLs in all columns from the right-hand side. With CROSS APPLY, the row on the left-hand side is removed from the result set.

cross apply是为用在给函数传值时，如果要把表或者视图里的很多值挨个传给函数，就用这个，如下：
select * from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s;

It Slices! It Dices! It Does It All!
通常我们会查询订单或工单或生产入库单，需要去关联这个产品的BOM结构：此时就可以利用cross apply很方便：