为什么我的 t-sql 左连接不起作用? [英] Why is my t-sql left join not working?

问题描述

你能运行这个并告诉我为什么结果集只有两行.它应该有三个，看起来像这样......

Can you run this and tell me why the result set only has two rows. It should have three and look like this...

appId    stepId       section       start
101      1           Section 1     2016-01-03 00:00:00.000
101      2           Section 2     2016-01-03 00:00:00.000
101      10          Section 3     NULL

这是sql，因此您可以将其粘贴到查询工具中

Here is the sql so you can just paste it into your query tool

create table #appSteps(stepId decimal, section nvarchar(50))
insert into #appSteps (stepId, section) values (1, 'Section 1')
insert into #appSteps (stepId, section) values (2, 'Section 2')
insert into #appSteps (stepId, section) values (3, null)
insert into #appSteps (stepId, section) values (4, null)
insert into #appSteps (stepId, section) values (10, 'Section 3')

create table #appProgress(stepId decimal, appId int, start datetime)
insert into #appProgress (stepId, appId, start) values (1, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (2, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (3, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (4, 101, '1/3/2016')


select p.appId, s.stepId, s.section, p.start
from #appSteps s with (nolock)
left join #appProgress p on s.stepId = p.stepId
where s.section is not null
and p.appId = 101

drop table #appSteps
drop table #appProgress

我不明白为什么 #appSteps 中的所有 3 个非空行都没有返回

I cannot figure out why all 3 non null rows from #appSteps are not coming back

推荐答案

原因是因为您在 WHERE 子句中包含了右侧表.您应该将其移至 LEFT JOIN 的 ON 条件:

The reason is because you are including the right-hand table in the WHERE clause. You should move that to the ON condition of the LEFT JOIN:

Select    P.appId, S.stepId, S.section, P.start
From      #appSteps    S   With (NoLock)
Left Join #appProgress P   On  S.stepId = P.stepId 
                           And P.appId = 101
Where     S.section Is Not Null

这样做的原因是因为 WHERE 子句在 LEFT JOIN 之后被评估，然后过滤掉你的 NULL 来自 LEFT JOIN 的结果.

The reason it does this is because the WHERE clause is evaluated after the LEFT JOIN, which then filters out your NULL results from the LEFT JOIN.

在 WHERE 子句中包含 LEFT JOIN 的右侧表(或 RIGHT JOIN 的左侧表)有效地将 OUTER JOIN 转换为 INNER JOIN.

Including the right-hand table of a LEFT JOIN (or the left-hand table of a RIGHT JOIN) in the WHERE clause effectively transforms the OUTER JOIN into an INNER JOIN.

这篇关于为什么我的 t-sql 左连接不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！