on子句与子选择中的谓词

问题描述

您好我们经营2016年的企业。我对在join" on子句中使用非列到列条件有点抵制。但是面对这个并且想知道选项2是否会使用连接中key1的索引。

从a.key1 = b上的左连接b中选择a。*，b。* .key1和b.deletedate为null

从左连接中选择一个。*，x。*（select * from b，其中deletedate为null）x on a.key1 = b.key1

我认为它不会改变这个问题，但我实际上在实际查询中实际上有大约10个左连接，每个都面临同样的问题。 我提到的唯一原因是我已经读过，在某些条件下，左连接实际上是由sql server转换为内部
连接。

解决方案

Hi we run 2016 enterprise. I'm a little resistant to using non column to column conditions in join "on clauses". But am faced with this and wonder if option 2 will use the indexes on key1 in the join.

select a.*,b.* from a left join b on a.key1=b.key1 and b.deletedate is null

select a.*,x.* from a left join (select * from b where deletedate is null) x on a.key1=b.key1

I don't think it will change the question but I actually have about 10 left joins in the actual query each facing the same issue.  The only reason I mention that is I have read that under certain conditions, a left join is actually turned into an inner join by sql server.

解决方案

这篇关于on子句与子选择中的谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！