17.05 - Coding ON Clauses With WHERE Clauses for Outer Joins - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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:

o.course_no e.emp_no
C100 236
C100 668
C200 ?
C400 ?

Note that 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:

o.course_no e.emp_no
C100 236
C100 668
C400 ?

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 not a join condition. It is a search 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 illustrates:

o.course_no e.emp_no
C100 236
C100 668
C200 ?
C400 ?

Note that BTEQ reports represent nulls with the QUESTION MARK character.

Although it was 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 obtaining unexpected results, you should generally use only ON clauses that reference columns from two tables that are to be joined. In other words, 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:

table_a.a table_a.b   table_b.a
3 1   3
6 6   6

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 table:

table_a.a table_a.b   table_b.a
  1   3
6 6   6

When the WHERE restriction is applied, the final results are reported as seen in the following table:

table_a.a table_a.b   table_b.a
6 6   6

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 table shows:

table_a.a table_a.b   table_b.a
6 6   6
3 1   ?

Note that BTEQ reports represent nulls with the QUESTION MARK character.

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

Using Search Conditions In ON Clauses

Some scenarios require a search condition in the ON clause. For example, to list all courses offered as well as the course requested by employee 236, you might 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:

Join Type Clause For Search Condition
Outer WHERE
Inner ON

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

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.

    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.

  • The best practice is to use only join conditions in ON clauses.

    A search condition in the ON clause of the inner table does not limit the number of rows in the answer set. 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 returned with a WHERE clause may not be obvious or intuitive. See Outer Join Case Study and the topics that follow.
  • Geospatial indexes cannot be used for outer joins. For more information on geospatial data, see SQL Geospatial Types.

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.