Database
Highlighted

## INNER VS OUTER JOINS effect of AND & WHERE

• 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.

Tags (4)