Left join with no columns selected from right table

Database
Enthusiast

Left join with no columns selected from right table

Hi All,

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 ?

Code :

select
AD2.*
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 ?

--Samir Singh

2 REPLIES
Junior Contributor

Re: Left join with no columns selected from right table

Hi Samir,

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.

Enthusiast

Re: Left join with no columns selected from right table

Hi Dieter,

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?

Thanks Samir