Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 17 18 19 20 21 22 < 23 > 24 25 26 27 28 29 .. 241 >> Next

Outer joins are in virtually all cases neither commutative nor associative. If you wish to do an outer join of three tables, you must specify the join order by placing the ON clause in an appropriate position within the FROM clause so that the join operation can be evaluated correctly.

The rule is: the first ON clause (from left to right) is evaluated first, and an ON clause applies to the immediately preceding join operation.

Thus, the following two join operations evaluate differently.

R LEFT OUTER JOIN S ON <join condition> RIGHT OUTER JOIN T ON <join condition>

If R, S and T are tables, then the above statement means that table R is left outer joined to table S according to the first join condition, and then the derived table (that is, the table that is the result of the first join operation, a new derived “left” table) is right outer joined to table T, according to the next join condition.

If we use parentheses to indicate order of evaluation, we would rewrite our example in this way:

(R LEFT OUTER JOIN S ON <join condition>) RIGHT OUTER JOIN T ON <join condition>

Note: Parentheses can be used in writing a select operation that performs an outer join on three or more tables.

3-8

Teradata RDBMS for UNIX SQL Reference
Joins: Working with Multiple Tables

Outer Join

Joining Four Tables

The Temporary Derived Table

But if the ON clause were placed differently, as in the following example then table S would be right outer joined to table T, according to the first join condition, and then the derived table (a new derived “right” table) would be left outer joined to table R, according to the next join condition.

R LEFT OUTER JOIN S RIGHT JOIN T ON <join condition> ON <join condition>

If we use parentheses to indicate order of evaluation, we would rewrite our example in this way:

R LEFT OUTER JOIN (S RIGHT JOIN T ON <join condition>) ON <join condition>

If we added a fourth table U to our example, as in the following join operation:

R LEFT OUTER JOIN S ON <join condition> JOIN T LEFT OUTER JOIN U ON <join condition> ON <join condition>

it would be evaluated this way: table R would be left outer joined to table S, according to the first join condition; then table T would be left outer joined to table U, according to the second join condition, and finally the two derived tables would be inner joined, according to the third join condition.

If we used parentheses, the above join statement could be rewritten as follows:

(R LEFT OUTER JOIN S ON <join condition>) JOIN (T LEFT OUTER JOIN U ON <join condition>) ON <join condition>

Notice that the join condition, specified in the ON clause for the inner join (simply called JOIN in our example) is separated from that operation by the join condition that specifies how the left outer join of tables T and U is to be performed.

When joining three or more tables, the join operation creates what is called a temporary derived table that is defined for the duration of the select operation. Such a temporary table is also called a joined table. The Teradata RDBMS (e.g., in EXPLAIN output) calls all temporary tables spool tables.

In the first example above:

R LEFT OUTER JOIN S ON <join condition> RIGHT OUTER JOIN T ON <join condition>

when table R is left outer joined to table S according to the first join condition, a derived table is created. It is that derived table, not a real table, that is then right outer joined (as a new derived left table) to table T, according to the second join condition.

Teradata RDBMS for UNIX SQL Reference

3-9
Joins: Working with Multiple Tables

Outer Join

Using ON Clauses Carefully

Different join conditions in a FROM clause yield 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, and to include in that query the following information:

• the courses offered

• their locations,

• classes requested but not offered

• employees who requested them.

In the SELECT statement that follows, the OFFERINGS table is full outer joined to the ENROLLMENT table, according to the first ON condition (Offerings.CourseNo = Enrollment.CourseNo).

SELECT Courses.CourseNo, Offerings.CourseNo, Offerings.Location Enrollment.CourseNo, Enrollment.EmpNo FROM Offerings FULL OUTER JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNO RIGHT OUTER JOIN Courses

ON Courses.CourseNo = Offerings.CourseNo

The result is shown below.

O.CourseNo E.CourseNo E.EmpNo
C100 C100 236
C100 C100 668
C200 Null Null
C400 Null Null
Null C300 236

Then the derived table (shown above) is right outer joined to the COURSES table, according to the second join condition (Courses.CourseNo = Offerings.CourseNo).

The results are:

C.CourseNo O.CourseNo E.CourseNo E.EmpNo
C100 C100 C100 236
C100 C100 C100 668
C200 C200 Null Null
Previous << 1 .. 17 18 19 20 21 22 < 23 > 24 25 26 27 28 29 .. 241 >> Next