Left Outer Join Does not preserve Left Table?

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.

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.

Nice..I knew about the order but didn't know Itzik had it in poster form..thanks – SQLMenace Sep 10 at 12:35 Sorry Dear @Martin Smith:I read in a web site that in LEFT JOIN the result record number equals to left table record number. I say if It can't evaluate WHERE predicate it return an exception or null for count. – Nima Sep 10 at 12:38 1 @Nima: In short, what you've read about joins is true, but that applies to joins alone.

Logically, the WHERE clause is evaluated after the joins, thus additional rows may get filtered out. – Andriy M Sep 10 at 13:39 1 @gbn - Well TOP ... ORDER BY is a bit weird as the ORDER BY both defines the TOP and the order of results, looks like Itzik did change his mind himself between versions there are other anomalies too like you can ORDER BY something not in the SELECT list. – Martin Smith Sep 10 at 14:50 1 MSDN has it for SQL Server 2008 but not for '2005 msdn.microsoft.Com/en-us/library/ms189499.

Aspx which shows correctly (IMO). – gbn Sep 10 at 15:07.

In essence you have created an inner join, change WHERE FUNCS = 1 to AND FUNCS = 1 The left outer joined cannot be specified in the WHERE clause otherwise it will be filtered out another problem is the LEN function on NULLs it won't be greater than 0.

1 wiki.lessthandot. Com/index. Php/WHERE_conditions_on_a_LEFT_JOIN – HLGEM Sep 10 at 16:08 Thanks for the link...I forgot it was there – SQLMenace Sep 10 at 16:14.

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

In essence you have created an inner join, change.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.