Coding ON Clauses with WHERE Clauses for Outer Joins - Teradata Vantage

Although an ON clause is required for each operation in an outer join in a FROM clause, an outer join can itself also include a WHERE clause.

Any restriction in the WHERE clause is applied only to the table that is the final result of the outer join. The WHERE clause does not define the join condition of the outer join.

Example: Join with WHERE Clause

Consider the following SELECT statement.

     SELECT offerings.course_no, enrollment.emp_no
     FROM offerings
     LEFT JOIN enrollment ON offerings.course_no = enrollment.course_no
     WHERE location = 'El Segundo';

This query yields the intermediate derived results in the following table:

BTEQ reports represent nulls with the QUESTION MARK character.

The query next applies the location restriction. After the restriction is applied (course C200 is given in Atlanta), the results are as seen in the following table:

Example: Outer Join with ON Clause

Suppose you were to put the location restriction in the ON clause, as in the following SELECT statement:

     SELECT offerings.course_no, enrollment.emp_no
     FROM offerings
     LEFT OUTER JOIN enrollment ON (location = 'El Segundo')
     AND (offerings.course_no = enrollment.course_no);

Such a restriction is a search condition, not a join condition..

The location restriction is applied as part of the inner join (of the left outer join) rather than after the left outer join has been performed.

When formed this way, the query produces confusing results, as the following table shows:

BTEQ reports represent nulls with the QUESTION MARK character.

Although applied as part of the inner join (of the left outer join), the location restriction did not eliminate course C200, given in Atlanta, from being returned. C200 was returned as part of the rows that were not returned as a result of the inner join.

Use Join Conditions in ON Clauses, Not Search Conditions

To avoid unexpected results, use only ON clauses that reference columns from two tables that are to be joined. That is, specify join conditions in ON clauses, and not search conditions. Also see Placing Search Conditions in a Join.

Example: WHERE Restriction Not Part of the Join Condition

Consider the following two tables, table_a:

And table_b:

The following SELECT statement yields these results. The left outer join returns the data in the table that follows:

     SELECT *
     FROM table_a
     LEFT OUTER JOIN table_b ON table_a.a = table_b.a
     WHERE table_a.b > 5;

The query yields the intermediate derived results in the following tables.

Table a:

Table b:

When the WHERE restriction is applied, the final results are reported as seen in the following tables.

Table a:

Table b:

Example: WHERE Restriction Part of the Join Condition

What happens if you include the WHERE restriction as part of the join condition? Consider the following SELECT statement:

     SELECT *
     FROM table_a
     LEFT OUTER JOIN table_b ON (table_a.b > 5)
     AND (table_a.a = table_b.a);

The results of this query are confusing, as the following results tables show.

Table a:

Table b:

BTEQ reports represent nulls with the QUESTION MARK character.

The join condition, which specified the inner join of the left outer join, restricts the results to 6, 6, 6. The rows that were not returned as a result of the inner join are returned and extended with nulls. Thus, the second row has 3, 1, null, which is not the desired result.

Using Search Conditions in ON Clauses

A scenario may require a search condition in the ON clause. For example, to list all courses offered and the course requested by employee 236, you may use the following query.

     SELECT offerings.course_no, enrollment.emp_no
     FROM offerings
     LEFT OUTER JOIN enrollment ON offerings.course_no =
                     enrollment.course_no
     AND enrollment.emp_no = 236;

Placing Search Conditions in a Join

The following table provides guidelines for placing the search condition for outer and inner joins:

In a left outer join, the outer table is the left table, and the inner table is the right table.

Rules for Coding ON and WHERE Clauses for Outer Joins

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. The Optimizer logically applies the WHERE clause condition only after a join has been produced. The actual application of conditions depends on how the Optimizer chooses to implement the query.

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