什么时候应该在内部联接上使用交叉应用？

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

#1 楼

谁能给我一个很好的例子，说明在CROSS APPLY在INNER JOIN也能起作用的情况下有所作为吗？比较：


INNER JOIN与CROSS APPLY

CROSS APPLY在没有简单JOIN条件的情况下效果更好。来自3的最后一条记录，来自t2的每条记录：类似于使用t1和window函数的功能： />
只需检查一下。

INNER JOIN是一个约CTE记录的表，在master上带有20,000,000。

此查询：

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

运行了将近PRIMARY KEY秒，而这一个：

q431 2078q

是即时的。

#2 楼

cross apply有时使您能够执行inner join不能执行的操作。

示例（语法错误）：错误，因为当与inner join一起使用时，表函数只能将变量或常量作为参数。 （即，表函数参数不能依赖于另一个表的列。）

但是：

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

这是合法的。

编辑：
或者使用较短的语法：（由ErikE提供）

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

编辑：

注意： br /> Informix 12.10 xC2 +具有横向衍生表，而Postgresql（9.3+）具有横向子查询，可以使用类似的效果。

#3 楼

考虑您有两个表。

主表

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

详细信息表

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

在很多情况下，我们需要将INNER JOIN替换为CROSS APPLY。

1。根据TOP n结果加入两个表

考虑是否需要从Id中选择Name和Master，并从Id中选择每个Details table的最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

<上面的查询生成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

，它生成了最后两个结果日期与最后两个日期为Id，然后仅在Id的外部查询中加入这些记录，这是错误的。这应该同时返回Ids 1和2，但它仅返回1，因为1具有最后两个日期。为此，我们需要使用CROSS APPLY。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

SQL FIDDLE

并形成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

这是它的工作方式。 CROSS APPLY内部的查询可以引用外部表，而INNER JOIN不能执行此操作（抛出编译错误）。找到最后两个日期时，将在CROSS APPLY即WHERE M.ID=D.ID内部进行合并。

2。当需要使用函数来实现INNER JOIN功能时。

当需要从CROSS APPLY表和INNER JOIN中获取结果时，可以将Master替换为function。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

这是函数

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

SQL FIDDLE

产生以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

交叉应用的附加优点

APPLY可以替代UNPIVOT。 CROSS APPLY或OUTER APPLY均可在此处使用，它们可以互换。

请考虑下表（名为MYTABLE）。查询如下。

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

SQL FIDDLE

为您带来结果

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

#4 楼

在我看来，CROSS APPLY在处理复杂/嵌套查询中的计算字段时可以填补一定的空白，并使它们更简单易读。提供了多个与年龄相关的字段，这些字段也将依赖其他数据源（例如，就业），例如Age，AgeGroup，AgeAtHiring，MinimumRetirementDate等，供最终用户应用程序使用（例如Excel PivotTables）。

选项是有限的并且很少优雅：


JOIN子查询不能基于父查询中的数据在数据集中引入新值（必须独立存在）。 br /> UDF很整洁，但速度较慢，因为它们往往会阻止并行操作。作为一个单独的实体可以是一件好事（更少的代码）也可以是一件坏事（代码在哪里）。
结点表。有时它们可​​以工作，但是很快您就可以与大量UNION一起加入子查询。大麻烦。
创建另一个单一用途的视图，假设您的计算不需要在主查询过程中获取的数据。
中间表。是的...通常可以正常工作，并且通常是一个不错的选择，因为它们可以被索引并且快速，但是由于UPDATE语句不平行并且不允许级联公式（重用结果）来更新内部的多个字段，因此性能也会下降相同的声明。有时您只想一遍就做。
嵌套查询。是的，您可以随时在整个查询上加上括号并将其用作子查询，在子查询上您可以操作源数据和计算字段。但是您只能在丑陋之前做很多事情。非常难看。

重复代码。 3个长（CASE ... ELSE ... END）语句的最大值是多少？


告诉您的客户自己计算该死的东西。



我错过了什么？可能吧，请随时发表评论。但是，在这种情况下，CROSS APPLY就像天赐之物：您只需添加一个简单的CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl和voilà！现在，您的新字段几乎可以像在源数据中一样使用了。

通过CROSS APPLY引入的值可以...


用于创建一个或多个计算字段而不会增加混合的性能，复杂性或可读性问题
与JOIN一样，随后的一些CROSS APPLY语句可以引用自己：由CROSS APPLY在随后的JOIN条件中引入的
作为奖励，它具有表值函数方面

Dang，没有什么可以做的！

#5 楼

从技术上来说，这已经得到很好的回答，但是让我举一个具体的例子说明它是如何非常有用的：让我们说您有两个表，Customer和Order。客户有很多订单。

我想创建一个视图，向我提供有关客户以及他们最近下的订单的详细信息。仅使用JOINS，这将需要一些自联接和聚合，这并不理想。但是使用Cross Apply，它超级简单：

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T

#6 楼

交叉应用同样适用于XML字段。如果希望与其他字段一起选择节点值。

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

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

使用查询

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

#7 楼

交叉应用可用于替换需要子查询列的子查询

子查询

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

这里我将无法选择公司表的列
，因此，使用交叉应用

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

#8 楼

我想应该是可读性;）

交叉应用对于阅读的人来说有些独特，告诉他们正在使用UDF，它将应用于左侧表格的每一行。 />
当然，还有其他限制，那就是CROSS APPLY比上面其他朋友已发布的JOIN更好地使用。

#9 楼

这是一篇文章，对它们进行了说明，并介绍了它们在JOINS上的性能差异和用法。对于正常的联接操作（内部和交叉）而言，它们之间的性能差异没有任何区别。 >

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”。”此处，在读取每一行时，会将值传递给函数。对我来说听起来很酷。 :)

#10 楼

好吧，我不确定这是否符合使用交叉申请与内部联接的理由，但是在使用交叉申请的论坛帖子中为我回答了此查询，因此我不确定是否使用内部联接的均等方法：

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

AS
BEGIN

-- 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

#11 楼

APPLY运算符的本质是允许FROM子句中运算符的左侧和右侧之间相关。

与JOIN相比，不允许输入之间的相关。

说到APPLY运算符中的相关性，我的意思是我们可以在右边放：


派生表-作为具有别名的相关子查询函数-具有参数的概念视图，其中参数可以引用左侧

两者都可以返回多个列和行。

#12 楼

这也许是一个古老的问题，但是我仍然喜欢CROSS APPLY的功能，它可以简化逻辑的重用并提供结果的“链接”机制。下面显示了一个简单的示例，说明如何使用CROSS APPLY对数据集执行复杂的逻辑运算而不会造成任何混乱。从这里推断出更复杂的计算并不难。

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

#13 楼

尽管大多数使用CROSS APPLY的查询都可以使用INNER JOIN进行重写，但是CROSS APPLY可以产生更好的执行计划和更好的性能，因为它可以限制在联接发生之前就被联接的集合。在这里

#14 楼

这是一个简短的教程，可以保存在.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;