I have a very simple question : If i have a join of two tables with a left outer join and i dont select any columns from the right table, then why does not the optimizer convert it to a Select * from left table only and avoid the join ?
from table1 AD2
left join tab2 HHD
on AD2.Col1 = HHD.Col1
For the code above, if you see the explain, it does an all AMP retrive from AD2 and left joins with HHD based on Col1 and Col2. But, technically as LOJ, i am selecting all the rows from AD2 as it is my left table and corresponding right table columns (which are NULL for non matching columns based on COl1 and Col2). But, since i am not selecting any columns from the right table, then it is as good as - Sel AD2.* from table1 AD2. So, should the optimizer not do so ?
both the same result set only when the inner table's join column is unique. This factt is unknown to the optimizer unless it's defined with a USI/PK.
But when you add DISTINCT it will eliminate the join.
I tested both the scenarios and they work as you explained. It does a retrieve from the left table only with joining to the right table. Is there any explanation to this behaviour ? any link where i can get some explanation to this?