Left join returns fewer rows than expected?

Question

Following is my query:

  Select Count(*)
         from Table1 s
               left join Table2 d
                    ON  s.subjectid = d.subjectid
                    and s.PROJECTID = d.projectid
                    and s.SITEName = d.SITENAME
               left join Table3 dev
                    on s.subjectid = dev.subjectid
                    and s.projectid = dev.projectid
                    and s.siteid = dev.siteid
         Where s.isprod =1
         and   d.isprod =1
         and  dev.isprod = 1
         and s.projectid =107
      -- Output 301 ROWS

This query returns 301 rows. However, if I don't use Table3 then the join returns 2203 rows, as shown in the query below:

         Select Count(*)
         from Table1 s
               left join Table2 d
                    ON  s.subjectid = d.subjectid
                    and s.PROJECTID = d.projectid
                    and s.SITEName = d.SITENAME
         Where s.isprod =1
         and   d.isprod =1
         and s.projectid =107
       -- OutPut 2203 ROWS

By my understanding of left join, all the rows from the left table should remain even if they don't match with the right table. However in this case, the number of rows is reduced from 2203 in query 2 to 301 in query 1. How is that possible?

Please suggest what could be going wrong here. For more clarification:

• Table1 with the same where conditions as above has 1300 rows

• Table2 with the same where conditions as above has 2203 rows

• Table3 with the same where conditions as above has 129 rows

Answer 1

When you have conditions in your where clause that put non-null constraints on the records from the table you have outer joined, you effectively destroy the effect of the outer join, and make it act as an inner join

The solution is to move such constraints into the on clause of the outer join:

Select Count(*)
     from Table1 s
           left join Table2 d
                ON  s.subjectid = d.subjectid
                and s.PROJECTID = d.projectid
                and s.SITEName = d.SITENAME
                and d.isprod =1
           left join Table3 dev
                on s.subjectid = dev.subjectid
                and s.projectid = dev.projectid
                and s.siteid = dev.siteid
                and dev.isprod = 1
     Where s.isprod =1
     and s.projectid =107

Answer 2

The first SQL has additional "where" clause of "and dev.isprod = 1". Most likely this is reducing the number of rows returned.