All Forums Database
DollarFromHash 1 post Joined 10/15
27 Oct 2015
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.
 

You must sign in to leave a comment.