Archives of the TeradataForum

Message Posted: Tue, 29 Jul 2003 @ 21:07:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

There are 2 parts to this:

First is just the SQL.
Inner (default) join is very different from outer joins in terms of the resultant set. In your case, the results were same and this was a coincidence based on the nature of the data in the tables.

Second part is the explain and the access paths for inner vs. outer. The optimizer has more freedom to come up with better plan for inner joins, compared to outer. From the manual - "Because inner joins are both commutative and associative, the Optimizer can select the best join order for inner joins arbitrarily and the end result is always identical. Outer joins, on the other hand, are rarely either commutative or associative. The Optimizer cannot select an arbitrary best join order for outer joins because neither commutativity nor associativity can be assumed, nor does it have any way to know what specific result you intended to produce with the query you presented to it."

Kumaran Anantaraman

	<Prev	Next>		<<First	<Prev	Next>	Last>>