Why Does My T-SQL LEFT JOIN Return Fewer Rows Than Expected?

T-SQL Left Join Not Yielding Expected Results

In this inquiry, a LEFT JOIN operation between two tables, #appSteps and #appProgress, is not returning the desired result set. The intended result set contains three rows, but the actual result has only two.

The provided SQL query is as follows:

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

The issue stems from the inclusion of the right-hand table (#appProgress) in the WHERE clause. To rectify this, the condition should be moved 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

This modification ensures that the WHERE clause is evaluated after the LEFT JOIN, preventing the filtering out of NULL results from the join. By placing the condition in the ON clause, the LEFT JOIN effectively behaves as intended, returning all rows from the left-hand table (#appSteps) with matching rows in the right-hand table (#appProgress) and NULLs for unmatched rows.

The above is the detailed content of Why Does My T-SQL LEFT JOIN Return Fewer Rows Than Expected?. For more information, please follow other related articles on the PHP Chinese website!