Left Outer Join Does not preserve Left Table

Question

I have this query:

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))) > 0
        AND LEN(LTRIM(RTRIM(po.UserNumber))) > 0
GROUP BY
   Q_ID,
   Q_DESC
ORDER BY
   Q_ID

---

the table #tmp_rep has 2 columns(Q_ID,Q_Desc) and 4 rows.and table po_Questions has 10 that use 3 Q_ID code in column Certificate 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:

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))) > 0
        AND LEN(LTRIM(RTRIM(po.UserNumber))) > 0

GROUP BY
   Q_ID,
   Q_DESC
ORDER BY
   Q_ID

then I get just 3 rows in the result and Q_ID=4 does not belong to result.Why SQL Server has this behaivior?

thanks

Answer 1

For the non matching rows po.UserName will be NULL so LEN(LTRIM(RTRIM(po.UserName))) is NULL

NULL > 0 evaluates to UNKNOWN not TRUE so when the predicate is in the WHERE you are turning your outer join back into an inner one. Similarly for FUNCS as SQLMenace points out.

You might want to Download Itzik Ben Gan's Logical Query Processing poster.

Conceptually the following happens (this should not be confused with how it is physically implemented however!)

For your first query:

• Cartesian Product on #tmp_rep, po_Questions
• Then the ON Filter is applied which effectively does an INNER JOIN on Q_ID = Certificate but also excludes any po_Questions rows that don't match your predicate.
• Then the non matching Outer Rows from #tmp_rep are added back in. These will have NULL for all columns from po_Questions
• There is no WHERE clause so this is the final result.

For your second query:

• Cartesian Product on #tmp_rep, po_Questions
• Then the ON Filter is applied which effectively does an INNER JOIN on Q_ID = Certificate.
• Then the non matching Outer Rows from #tmp_rep are added back in. These will have NULL for all columns from po_Questions
• Then the WHERE clause is evaluated. This will definitely remove all rows from the previous step and possibly additional rows too.