on子句与子选择中的谓词 [英] predicate in on clause vs in sub select

问题描述

您好我们经营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转换为内部
连接。

解决方案

>>左连接实际上是由sql server转换为内连接。

如果您放入  b.deletedate在WHERE子句中为null

>>>如果选项2将使用连接中key1的索引。

是的，它应该但你为什么要使用SELECT *。 为什么你没有WHERE子句？

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.

解决方案

>>a left join is actually turned into an inner join by sql server.

Right if you put b.deletedate is null in WHERE clause

>>> if option 2 will use the indexes on key1 in the join.

Yes, it should but why do you use SELECT *.  and why do not you have a WHERE clause?

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