sql - 我什么时候应该使用Inner Join?

  显示原文与译文双语对照的内容

使用交叉应用的主要目的是什么

我已经阅读了( 很模糊,通过互联网上的文章),当你正在进行分区时,cross apply 可以更高效地选择大数据集。 ( 分页浮现)

我也知道 cross apply不需要一个UDF作为 right-table 。

在大多数 INNER JOIN 查询( one-to-many关系) 中,我可以重写它们以使用 cross apply,但它们总是给我等价的执行计划。

在这些情况下,从中 INNER JOIN 仍可以正常工作well,谁能给我一个很好的例子,当 cross apply 进行一些什么?


编辑:

下面是一个简单的例子,其中执行计划完全相同。 ( 向我展示它们不同的地方,以及 cross apply的速度更快)


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

时间:

谁能告诉我一个很好的例子,当crossapply意义非凡在这些情况下的内部联接将也可以使用,在哪?

有关详细性能比较,请参阅我博客中的文章:

CROSS APPLY 在没有简单 JOIN 条件的情况下效果更好。

从最后一个记录从这一个选择 3 于每个记录相关


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

不能用 INNER JOIN 条件很容易地创建它。

你可以使用 CTE 和窗口函数来执行类似的操作:


WITH t2o AS
 (
 SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
 FROM t2
 )
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
 t2o
ON t2o.t1_id = t1.id
 AND t2o.rn <= 3

,但这是不可以读的,可能是低效的。

更新:

刚刚检查。

master 是关于 20,000,000 记录的表格,其中包含 PRIMARY KEY

这里查询:


WITH q AS
 (
 SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
 FROM master
 ),
 t AS 
 (
 SELECT 1 AS id
 UNION ALL
 SELECT 2
 )
SELECT *
FROM t
JOIN q
ON q.rn <= t.id

运行时间几乎为 30 秒,而这个值为:


WITH t AS 
 (
 SELECT 1 AS id
 UNION ALL
 SELECT 2
 )
SELECT *
FROM t
CROSS APPLY
 (
 SELECT TOP (t.id) m.*
 FROM master m
 ORDER BY
 id
 ) q

是即时的。

cross apply 有时允许你做那些你不能用 inner join 做的事情。

示例( 语法错误):


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

使用这是一个语法错误 when.,因为表函数只能接受变量或者常量作为参数

然而:


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

这是合法的。

编辑: 或者或者,更短的语法: ( 按 ErikE )


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

下面是交叉应用与性能产生巨大差异的一个例子:

使用交叉来优化条件之间的联接

注意,除了替换内部联接之外,还可以重用代码,比如在involing标量udf不支付性能惩罚的情况下截断日期,例如: 计算本月第三星期三的内联 udf

在复杂/嵌套查询中使用计算字段时,交叉应用可以填补一定的差距,并使它们更简单更易读。

简单示例:你有一个 DoB,你想要呈现多个age-related字段,这些字段也依赖于其他数据源的( 比如就业),AgeGroup,AgeAtHiring,MinimumRetirementDate,等等,用于最终用户应用程序( Excel数据透视表,例如) 。

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

  • 联接子查询不能基于父查询( 它必须站在自己的立场上) 中的数据在数据集中引入新值。

  • udf很整洁,但它们往往会阻止并行操作。 作为一个独立的实体可以是一个好的( 更少代码) 或者一个坏的( 代码在哪里) 事物。

  • Look-up表。有时它们可以工作,但很快你就可以加入带有大量联合的子查询了。 大混乱。

  • 创建另一个single-purpose视图,假设你的计算不需要通过你的主查询获得mid-way数据。

  • 中间表。是的,通常是有效的,通常是一个不错的选项,因为它们可以被索引,但性能也会下降,因为更新语句并不允许在同一语句中更新多个字段。 有时候你只希望在一次通过。

  • 嵌套查询。是的,可以在整个查询中放置圆括号,并将它用作一个子查询,你可以在该查询上操作源数据和计算字段。 但是你只能在它变得丑陋之前这么做。 非常难看。

  • 重复代码。3长( 案例。。或者。。结束) 语句的最大值是什么? 这将是可以读的 !

    • 告诉你的客户自己计算这些东西。

我错过了什么也许,可以自由评论。 但是,在这种情况下,交叉应用就像是无价之宝: 你只需添加一个简单 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl 和 voilà 你的新字段现在就可以使用了,就像它一直在源数据中一样。

通过交叉应用引入的值可以。。

  • 用于创建一个或者多个计算字段,而不将性能,复杂性或者可读性问题添加到混合中
  • 联接一样,随后的几个交叉应用语句可以引用它们自己: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • 在后续联接条件中,可以使用交叉应用的值
  • 作为奖励,还有Table-valued函数方面

可恶,他们什么都不能做 !

交叉应用同样适用于一个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

我想应该是可读性;)

交叉应用对于读取的人来说有些独特,告诉他们正在使用一个 UDF,它将应用到左边的每一行。

当然,当交叉应用比其他朋友在上面发布的更多时,还有其他限制。

考虑你有两个表。

主表


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

有很多情况,我们需要在替换

1.。基于 TOP n 结果联接两个表

于每个 Id 从相关考虑如果我们需要选择从 MasterIdName 和最后两个 dates.


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,这就是wrong,上看到时,它生成的结果中只填写日期时使用最后两个 Id的最小两日期然后参加上述记录。 要做到这一点,我们需要使用 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

和他以下结果的形式。


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 无法执行 this(throws compile error) 。 在查找最后两个日期时,在 CROSS APPLY IE 中进行连接,WHERE M.ID=D.ID

2.当我们需要使用函数的INNER JOIN 功能时。

在表和一个 MasterCROSS APPLY 可以与 INNER JOIN 当我们需要获得一个用来替换 result.


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
)

生成了以下结果


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

crossapply 额外的优势,

APPLY 可以用作 UNPIVOT的替换。 在这里可以使用 CROSS APPLY 或者 OUTER APPLY,它们可以互换。

考虑到下面的table(named 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

查询在下面。


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

这就给你带来了结果


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

我不确定这是否符合使用交叉应用的交叉应用,但是这个查询在使用交叉应用的论坛帖子中得到了回答,所以我不确定是否有使用内部联接的equalivent方法:


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

结尾

交叉应用可以用于替换需要子查询的子查询的位置

子查询


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

...