Why left join on is not returning the not-matching records?

testy 0
Viewed 41
select sr.Name, srj.Name as ParentSR from Role sr
left join Role srj
on sr.id= srj.ParentRoleId
where sr.isDeleted= 0 and srj.isDeleted= 0

now I have 190 records and among which only 40 have ParentRoleId but when I run this query it returns me only 40 the same which have only parentID but not the ones with ParentRoleID = NULL. I want it to return every row from the Role sr but it’s not happening.

tsql stored-procedures sql-server sql
1 Answers

When using a left outer join, and you want rows where that join may not have a match, then you must be careful that your where clause does not suppress the unmatched rows from the final result.

One way to achieve this is to convert predicates into extra join conditions

select sr.Name, srj.Name as ParentSR 
from Role sr
left join Role srj
       on sr.id = srj.ParentRoleId
       and srj.isDeleted = 0           --<< was in the where clause
where sr.isDeleted= 0

Or to explicitly allow for NULLs in the where clause e.g.

select sr.Name, srj.Name as ParentSR 
from Role sr
left join Role srj
       on sr.id = srj.ParentRoleId  
where sr.isDeleted= 0
    and (srj.isDeleted = 0 or srj.ParentRoleId IS NULL)
Paul Maxwell0
Related