Different join conditions in a FROM clause can yield very different results.
- 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.