Coding ON Clauses for Outer Joins - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Different join conditions in a FROM clause can yield very different results.

For example, suppose you wish to find out, using the three tables courses, offerings, and enrollment, whether the current course offerings satisfy the employee requests for courses, including the following information in the query.
  • Courses offered
  • Course locations
  • Courses requested but not offered
  • Employees who requested courses

Example: Full Outer Join and Right Outer Join

In the SELECT statement that follows, the offerings table is full outer joined to the enrollment table according to the first ON condition (offerings.course_no = enrollment.course_no).

     SELECT courses.course_no, offerings.course_no, offerings.location,
            enrollment.course_no, enrollment.emp_no
     FROM offerings
     FULL OUTER JOIN enrollment ON offerings.course_no =
                     enrollment.course_no
     RIGHT OUTER JOIN courses ON courses.course_no =
                      offerings.course_no;

The result of this intermediate join is shown in the following table:

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

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

This intermediate derived table is next right outer joined to the courses table, according to the second join condition (courses.course_no = offerings.course_no).

The final results appear in the following table:

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

Example: Right Outer Join of Courses and Enrollment

If the same SELECT statement is written with a different second join condition, as illustrated in the current example, then the offerings table is full outer joined to the enrollment table, according to the first JOIN condition (offerings.course_no = enrollment.course_no).

     SELECT courses.course_no, offerings.course_no,
            enrollment.course_no, enrollment.emp_no
     FROM offerings
     FULL OUTER JOIN enrollment ON offerings.course_no =
                     enrollment.course_no
     RIGHT OUTER JOIN courses ON courses.course_no =
                      enrollment.course_no;

The result of this intermediate join is shown in the following table:

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

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

This intermediate derived table is next right outer joined to the courses table, according to the second join condition (courses.course_no = enrollment.course_no).

The final results appear in the following table:

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

Comparing the ON Clause Join Conditions of the Examples

Although the following second join conditions might intuitively appear to be equivalent, the results of a SELECT are different using the two different join conditions.

Join Condition Example
courses.course_no = offerings.course_no Example: Full Outer Join and Right Outer Join
courses.course_no = enrollment.course_no Example: Right Outer Join of Courses and Enrollment

Using courses.course_no=offerings.course_no as the second join condition, you do not see in the results of the outer join, for example, that employee 236 registered for course C300.

But if you use courses.course_no=enrollment.course_no as the second join condition, you do not see in the results of the outer join, for example, that courses C200 and C400 are, in fact, offered.

For information on using WHERE clauses with ON clauses, see Coding ON Clauses With WHERE Clauses for Outer Joins.