Why are Rows Missing from my Left Join Result?

Left Join Discrepancy: Rows Omitted

In this query, the LEFT JOIN between tables #appSteps and #appProgress is malfunctioning, resulting in the exclusion of expected rows from the result set.

Query in Question:

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

Expected Result:

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

Actual Result:

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

Resolution:

The erroneous behavior arises from including the right-hand table (#appProgress) in the WHERE clause. By moving this condition to the ON clause of the LEFT JOIN, the desired result can be achieved:

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

In a LEFT JOIN, the WHERE clause is executed after the join, meaning that rows from the right-hand table that don't meet the WHERE clause criteria will be excluded from the result set. Including the right-hand table in the WHERE clause effectively transforms it into an INNER JOIN, filtering out expected results.

The above is the detailed content of Why are Rows Missing from my Left Join Result?. For more information, please follow other related articles on the PHP Chinese website!