交叉应用也适用于XML字段。如果您希望结合其他字段选择节点值。

例如，如果您有一个包含一些xml的表

根> < < subnode1 > <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> < / subnode1 > 根> < /

使用查询

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id

将返回一个结果

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

这可能是一个老问题，但我仍然喜欢CROSS APPLY的强大功能，它简化了逻辑的重用，并为结果提供了一种“链接”机制。

我在下面提供了一个SQL Fiddle，它展示了一个简单的示例，说明如何使用CROSS APPLY对数据集执行复杂的逻辑操作，而不会让事情变得一团糟。从这里不难推断出更复杂的计算。

http://sqlfiddle.com/ !3/23862/2

我们使用CROSS APPLY从另一个(更新请求)表中使用JSON更新一个表—join不能用于此，因为我们使用OPENJSON来读取JSON的内容，而OPENJSON是一个“表值函数”。

我本打算在这里放一个简化版的UPDATE命令作为示例，但即使是简化了，作为一个示例，它也太大、太复杂了。因此，这个简单的命令部分“草图”就足够了:

SELECT  
       r.UserRequestId,
       j.xxxx AS xxxx,
FROM  RequestTable as r WITH (NOLOCK)
   CROSS APPLY
      OPENJSON(r.JSON, '$.requesttype.recordtype')
      WITH(
             r.userrequestid nvarchar(50) '$.userrequestid',
             j.xxx nvarchar(20) '$.xxx
           )j
       WHERE r.Id > @MaxRequestId
          and ... etc. ....

好吧，我不确定这是否有资格作为使用交叉应用与内部连接的原因，但这个查询在论坛帖子中使用交叉应用为我回答了，所以我不确定是否有一个等效的方法使用内部连接:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

作为 开始

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp

END

这是一篇解释这一切的文章，以及它们在join中的性能差异和用法。

SQL Server交叉应用和外部应用连接

正如本文所建议的，对于正常的连接操作(INNER和CROSS)，它们之间没有性能差异。

当你必须执行这样的查询时，使用差异就出现了:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

也就是说，当你必须和函数联系起来的时候。这不能使用INNER JOIN来完成，这会给您一个错误“不能绑定多部分标识符”D.DepartmentID。在这里，读取每一行时将值传递给函数。听起来很酷。：）

下面是一个简短的教程，可以保存在.sql文件中，并在SSMS中执行，这是我为自己编写的，可以快速刷新我对CROSS APPLY如何工作以及何时使用它的记忆:

-- Here's the key to understanding CROSS APPLY: despite the totally different name, think of it as being like an advanced 'basic join'.
-- A 'basic join' gives the Cartesian product of the rows in the tables on both sides of the join: all rows on the left joined with all rows on the right.
-- The formal name of this join in SQL is a CROSS JOIN.  You now start to understand why they named the operator CROSS APPLY.

-- Given the following (very) simple tables and data:
CREATE TABLE #TempStrings ([SomeString] [nvarchar](10) NOT NULL);
CREATE TABLE #TempNumbers ([SomeNumber] [int] NOT NULL);
CREATE TABLE #TempNumbers2 ([SomeNumber] [int] NOT NULL);
INSERT INTO #TempStrings VALUES ('111'); INSERT INTO #TempStrings VALUES ('222');
INSERT INTO #TempNumbers VALUES (111); INSERT INTO #TempNumbers VALUES (222);
INSERT INTO #TempNumbers2 VALUES (111); INSERT INTO #TempNumbers2 VALUES (222); INSERT INTO #TempNumbers2 VALUES (222);

-- Basic join is like CROSS APPLY; 2 rows on each side gives us an output of 4 rows, but 2 rows on the left and 0 on the right gives us an output of 0 rows:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
    -- Note: this also works:
    --#TempStrings st CROSS JOIN #TempNumbers nbr

-- Basic join can be used to achieve the functionality of INNER JOIN by first generating all row combinations and then whittling them down with a WHERE clause:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
WHERE
    st.SomeString = nbr.SomeNumber

-- However, for increased readability, the SQL standard introduced the INNER JOIN ... ON syntax for increased clarity; it brings the columns that two tables are
-- being joined on next to the JOIN clause, rather than having them later on in the WHERE clause.  When multiple tables are being joined together, this makes it
-- much easier to read which columns are being joined on which tables; but make no mistake, the following syntax is *semantically identical* to the above syntax:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Inner join
    #TempStrings st INNER JOIN #TempNumbers nbr ON st.SomeString = nbr.SomeNumber

-- Because CROSS APPLY is generally used with a subquery, the subquery's WHERE clause will appear next to the join clause (CROSS APPLY), much like the aforementioned
-- 'ON' keyword appears next to the INNER JOIN clause.  In this sense, then, CROSS APPLY combined with a subquery that has a WHERE clause is like an INNER JOIN with
-- an ON keyword, but more powerful because it can be used with subqueries (or table-valued functions, where said WHERE clause can be hidden inside the function).
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr

-- CROSS APPLY joins in the same way as a CROSS JOIN, but what is joined can be a subquery or table-valued function.  You'll still get 0 rows of output if
-- there are 0 rows on either side, and in this sense it's like an INNER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- OUTER APPLY is like CROSS APPLY, except that if one side of the join has 0 rows, you'll get the values of the side that has rows, with NULL values for
-- the other side's columns.  In this sense it's like a FULL OUTER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st OUTER APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- One thing CROSS APPLY makes it easy to do is to use a subquery where you would usually have to use GROUP BY with aggregate functions in the SELECT list.
-- In the following example, we can get an aggregate of string values from a second table based on matching one of its columns with a value from the first
-- table - something that would have had to be done in the ON clause of the LEFT JOIN - but because we're now using a subquery thanks to CROSS APPLY, we
-- don't need to worry about GROUP BY in the main query and so we don't have to put all the SELECT values inside an aggregate function like MIN().
SELECT
    st.SomeString, nbr.SomeNumbers
FROM
    #TempStrings st CROSS APPLY (SELECT SomeNumbers = STRING_AGG(tempNbr.SomeNumber, ', ') FROM #TempNumbers2 tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr
-- ^ First the subquery is whittled down with the WHERE clause, then the aggregate function is applied with no GROUP BY clause; this means all rows are
--   grouped into one, and the aggregate function aggregates them all, in this case building a comma-delimited string containing their values.

DROP TABLE #TempStrings;
DROP TABLE #TempNumbers;
DROP TABLE #TempNumbers2;