Hi, All

I have the following SQL:

select i.INVOICE_DATE, i.INVOICE_NO, i.SHIPPING_CHARGE,
i.DIVISION_ID, a.ACCOUNT_NO, a.NAME, d.NAME,
i.KIND, i.SHIPPING_GST + i.GST, i.NETSALES, cd.PAYMENT_DISCOUNT
from INVOICE i
inner join CLIENTS c on c.ID = i.CLIENT_ID
inner join CONTACTS a on a.ID = c.CONTACT_ID and
(:clientgroupid <= 0 or a.CLIENTGROUP_ID = :clientgroupid)
inner join DIVISION d on d.ID = i.DIVISION_ID
inner join CLIENT_DIVISION cd on cd.CLIENT_ID = i.CLIENT_ID and
cd.DIVISION_ID = i.DIVISION_ID
where i.INVOICE_DATE between :startdate and :enddate and
i.POSTED = 'Y' and i.REVERSEID is null
order by i.DIVISION_ID, i.INVOICE_NO

The Plan for above SQL: A NATURAL, C FK_CONTACT_ID, why is that? I
suppose that A PK_CONTACT, and C PK_CLIENT

If I changed the above 'inner join CLIENTS c on c.ID = i.CLIENT_ID' to
'left join CLIENTS c on c.ID = i.CLIENT_ID'
then suddenly the PLAN changed to C PK_CLIENT, A PK_CONTACT, why?

it looks to me that Plan for left join is better than inner join, how
does that happen?

Thanks

William