Archives of the TeradataForum

Message Posted: Mon, 17 Dec 2007 @ 21:59:40 GMT

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

Sorry to make a RTFM reply, but this is one case when it's very appropriate since it's complicated and even the manual calls it 'non- intuitive'. The bottom line is that placing a search condition on the inner table in the WHERE clause turns an outer join into an inner join. All this is covered in the SQL Reference pp 2-34 to 2-37, but I'm pasting in the most relevant section.

Rules And Recommendations for Coding ON and WHERE Clauses for Outer Joins

The following rules and recommendations apply to coding ON and WHERE clauses for outer joins:

* One or more join conditions, or connecting terms, are required in the ON clause for each table in an outer join.

These join conditions define the rows in the outer table that take part in the match to the inner table.

* The best practice is to use only join conditions in ON clauses. However, when a search condition is applied to the inner table in a WHERE clause, it should be applied in the ON clause as well.

A search condition in the ON clause of the inner table does not limit the number of rows in the answer set. Instead, it defines the rows that are eligible to take part in the match to the outer table.

* An outer join can also include a WHERE clause; however, the results you get with a WHERE clause may be surprising and not obvious or even intuitive. This is explained in more detail in the case study (see "Outer Join Case Study" on page 2-38 and the topics that follow) using specific examples.

To limit the number of qualifying rows in the outer table (and therefore the answer set), the search condition for the outer table must be in the WHERE clause. Note that the Optimizer logically applies the WHERE clause condition only after a join has been produced. The actual application of conditions always depends on how the Optimizer chooses to implement the query.

If a search condition on the inner table is placed in the WHERE clause, the join is logically equivalent to an inner join, even if you explicitly specify the keywords LEFT/RIGHT/FULL OUTER JOIN in the query. The Optimizer always treats such a join as an inner join to simplify the query, rewriting it to roll the entire complex process into a single step.

JL

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