How to Optimize outer JOIN
With the outer join, some table reordering is possible and recommended for efficiency. Normally, hierarchical structures are built top-down, but when subviews are used, right-sided nesting can cause the structure to be built bottom-up. Top-down execution is more efficient than bottom-up execution because bottom-up execution can cause throwaways. Throwaways are rows that are retrieved into the working set and then later discarded. SQL Expanded (Bottom-Up): SELECT* FROM Department LEFT JOIN Employee LEFT JOIN Dependent ON EmpNo=DpndEmpNo ON DeptNo=EmpDeptNo SQL Rewritten (Top-Down): SELECT * FROM Department LEFT JOIN Employee ON DeptNo=EmpDeptNo LEFT JOIN Dependent ON EmpNo=DpndEmpNo