Why is my t-sql left join not working?

Solution 1:

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

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.

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.