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.
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
|
?
|
?
|
?
|
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 |
? |
? |
? |