title: Left Outer Join Does not preserve Left Table
body: <p>I have this query:</p>
  
  <pre><code>SELECT Q_ID,
     Q_DESC,
     COUNT(Q_ID)
  FROM   #tmp_rep
     LEFT OUTER JOIN po_Questions po
          ON  Q_ID = Certificate AND FUNCS = 1
          AND LEN(LTRIM(RTRIM(po.UserName))) &gt; 0
          AND LEN(LTRIM(RTRIM(po.UserNumber))) &gt; 0
  GROUP BY
     Q_ID,
     Q_DESC
  ORDER BY
     Q_ID
  </code></pre>
  
  <hr>
  
  <p>the table #tmp_rep has 2 columns(Q_ID,Q_Desc) and 4 rows.and table po_Questions has 10 that <strong>use 3 Q_ID code in column Certificate</strong> rows. If I run this query every thing is ok and for Q-ID=4 I get 0 for count,but If I wrote that query this way:</p>
  
  <pre><code>SELECT Q_ID,
     Q_DESC,
     COUNT(Q_ID)
  FROM   #tmp_rep
     LEFT OUTER JOIN po_Questions po
          ON  Q_ID = Certificate 
  WHERE FUNCS = 1
        AND LEN(LTRIM(RTRIM(po.UserName))) &gt; 0
          AND LEN(LTRIM(RTRIM(po.UserNumber))) &gt; 0
  
  GROUP BY
     Q_ID,
     Q_DESC
  ORDER BY
     Q_ID
  </code></pre>
  
  <p>then I get just 3 rows in the result and Q_ID=4 does not belong to result.Why SQL Server has this behaivior?</p>
  
  <p>thanks</p>