Why is my T-SQL LEFT JOIN only returning two rows instead of three?

Left Join Not Yielding Expected Results

The provided T-SQL code aims to perform a left join between two tables, #appSteps and #appProgress, but the result set contains only two rows instead of the expected three.

Issue

The issue arises because the right-hand table (#appProgress) is included in the WHERE clause:

where s.section is not null
and p.appId = 101

Explanation

A LEFT JOIN is intended to return all rows from the left table, including those without matching records in the right table. However, by including the right-hand table in the WHERE clause, the query effectively operates like an INNER JOIN, filtering out rows from the left table without matching records in the right table.

Solution

To obtain the desired result set, move the filter condition for the right-hand table to the ON clause 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

By placing the condition in the ON clause, matching rows from both tables are joined first, and then the WHERE clause is applied to further filter the results, ensuring that only rows with non-null values for section are returned.

The above is the detailed content of Why is my T-SQL LEFT JOIN only returning two rows instead of three?. For more information, please follow other related articles on the PHP Chinese website!