On Aug 2, 2013, at 11:25 AM, "Louis Kleiman (SSTMS, Inc.)" <lkleiman@...> wrote:

> But LEFT OUTER JOIN may cause the optimizer to scan through the table for
> which all rows are being included in physical order while the INNER JOIN
> might cause a plan where rows are read based on a scan through an index. If
> rows aren't being filtered out, I can see how this might speed up the query
> execution.
>
>>>
>>>
>>> but LEFT JOIN is diferente of JOIN...
>>> - LEFT JOIN brings all the data filtered from the FROM table, and
>>> - JOIN brings only the data that have the ON condition in the JOINED
>> table.
>>
>> Which should make JOIN faster than LEFT JOIN, if anything, because it
>> can (potentionally) filter out some records.
>>

To understand what's happening, you must look at the plan for the join. An outer join forces the order of the operation - outer table first, then inner table. Depending on the indexes and indexed conjuncts, the order you set in the outer join may lead to a fast join, or not. If outer joins work well for you, I'd guess that you know your data and that (maybe) the statistics on your indexes are bad. In theory, the optimizer should choose the best order for inner joins, but if you have better information than it does, outer joins will be faster.

Outer joins do not imply full table scans in physical order. Conditions in the WHERE clause that apply to the outer table and involve indexed terms are applied first. In this query

select custName
from Customers C
left outer join Orders O on O.custID = C.custID
where C.state = 'CT' and C.lastName = 'Smith'

the optimizer will use the index on C.state and the index on C.lastName to prequalify Customers.

Good luck,

Ann

<commercial note>(if you need more than luck, try IBPhoenix)</commercial note>