INNER VS OUTER JOINS effect of AND & WHERE

Database

INNER VS OUTER JOINS effect of AND & WHERE

My Undesrstanding was this 

  • WHERE CLAUSE on an INNER JOIN CONDITION Vs  INNER JOIN  WITH AND <condition> will be the same 

e.g. A Inner Join B on A.x=B.y WHERE B.z between Date1 and Date2 

and 

e.g. A Inner Join B on A.x=B.y AND B.z between Date1 and Date2 

  • For LOJ the AND condition is evaluated 1st 

e.g. A Left Join B on A.x=B.y WHERE B.z between Date1 and Date2 

and 

e.g. A Left Join B on A.x=B.y AND B.z between Date1 and Date2 

fetch different results because the 2nd conditon get evaluated pre Join

I have a  report that looks like this 

sel 

A.a1,

A.a2,

Case statement on  B.x and B.y

, C.c1

, C.c2

, SUM (A.a3),

,SUM (A.a4) 

some more  columns  in D , E , F , G 


From 

A  Fact Inner Join B  on ________

Inner Join C on ___________

Left Join D on _____________ 

Inner Join E on E.E1=A.A9  

Inner Join F  on ________

Left Outer Join G on ________

Left join ( derived table from D  ) dx on ____________

WHERE E.E7 between date1 and Date2 

and H.H3 in  < Large List > 

and <some other string and integer check conditions>

 The  Count ( *)  IS NOT the same if I rewrite as 

From 

A  Fact Inner Join B  on ________

Inner Join C on ___________

Left Join D on _____________ 

Inner Join E on E.E1=A.A9  

Inner Join F  on ________

Left Outer Join G on ________

Left join ( derived table from D  ) dx on ____________

AND E.E7 between date1 and Date2 

and H.H3 in  < Large List > 

and <some other string and integer check conditions>

 The  Count ( *)  IS NOT the same if I rewrite as . Here I moved the AND E1 condition right near the Inner Join E1 clause. 

From 

A  Fact Inner Join B  on ________

Inner Join C on ___________

Left Join D on _____________ 

Inner Join E on E.E1=A.A9  

AND E.E7 between date1 and Date2 

Inner Join F  on ________

Left Outer Join G on ________

Left join ( derived table from D  ) dx on ____________

where H.H3 in  < Large List > 

and <some other string and integer check conditions>

Can you help me visualize whats happening. Under what condition they are same and when they'd change.