Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 16 17 18 19 20 21 < 22 > 23 24 25 26 27 28 .. 241 >> Next

FROM Offerings LEFT JOIN Enrollment ON Offerings.CourseNo = Employee.CourseNo;

Teradata RDBMS for UNIX SQL Reference

3-5
Joins: Working with Multiple Tables

Outer Join

Right Outer Join

In a right outer join, the rows from the right table (the table specified to the right of the keywords OUTER JOIN) which are returned in the result of the inner join will be returned in the outer join result and extended with nulls.

If we perform a right outer join on the OFFERINGS and ENROLLMENT tables, the rows from the right table which are not returned in the result of the inner join are returned in the outer join result and extended with nulls.

The following SELECT statement yields the results in the following table.

SELECT Offerings.CourseNo, Offerings.Location,

Enrollment.EmpNo

FROM Offerings RIGHT OUTER JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNo;

O.CourseNo O.Location E.EmpNo
C100 El Segundo 236
C100 El Segundo 668
Null Null 236

These results show that course C100 has two employees enrolled in it and that employee 236 has not enrolled in another class. But in this case the nulls returned by the right outer join of the OFFERINGS and ENROLLMENT table are deceptive, since we know by inspection (of the ENROLLMENT table) that employee 236 has enrolled for course C300. We also know by inspection (of the OFFERINGS table) that course C300 is not currently being offered.

To get the results that we probably want, the right outer join should be written:

SELECT Enrollment.CourseNo, Offerings.Location,

Enrollment.EmpNo

FROM Offerings RIGHT OUTER JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNo;

Instead of the row (Null, Null, 236), the above SELECT returns the following row: C300, Null, 236.

When constructing an outer join, the projected column list has to be constructed carefully. In general, you would want to project the column from the same side as the outer join. In the example above, we are doing a right outer join on CourseNo. Therefore, we want to project out the right join column, which is Enrollment.CourseNo. In the subsequent full outer join example (see below), we are projecting both CourseNo columns for this same reason.

3-6

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

Outer Join

Full Outer Join

In a full outer join, rows from both tables that have not been returned in the result of the inner join will be returned in the outer join result and extended with nulls.

For example, suppose we wish to find out if the current course offering by customer education satisfied the employee requests for courses, and include the courses offered and their locations, as well as the classes requested but not offered and the employees who requested them.

For our example, we can use our same two tables, OFFERINGS and ENROLLMENT.

OFFERINGS

ENROLLMENT

CourseNo Beginning Dates Location
C100 01/05/94 El Segundo
C200 07/02/94 Dayton
C400 10/07/94 El Segundo

EmpNo CourseNo
236 C100
236 C300
668 C100

If we perform a full outer join on these two tables, the rows from both tables not returned in the result of the inner join are returned in the outer join and extended with nulls.

The following SELECT statement yields the desired results, shown in the table that follows.

SELECT Offerings.CourseNo, Offerings.Location, Enrollment.CourseNo, Enrollment.EmpNo,

FROM Offerings FULL OUTER JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNo;

O.CourseNo O.Location E.CourseNo E.EmpNo
C100 El Segundo C100 236
C100 El Segundo C100 668
C2 0 0 Dayton Null Null
C4 0 0 El Segundo Null Null
Null Null C300 236

These results show that course C100 has two employees enrolled in it and that course C200 and C400 have no employees enrolled in them. In this case, the nulls returned by the outer join of the OFFERINGS and ENROLLMENT table are meaningful values.

We see that course C300, for which employee 236 registered is not among those courses offered by customer education, nor does it

Teradata RDBMS for UNIX SQL Reference

3-7
Joins: Working with Multiple Tables

Outer Join

Order of Evaluation in Outer Joins

Joining Three Tables

have a location. Notice the null in the last row of the O.CourseNo and the O.Location columns.

Unlike inner joins, outer joins are in virtually all cases neither commutative nor associative. The result of an inner join of two tables is not changed if rows from table A are joined to rows from table B, or the reverse (the commutative principle).

The result of an inner join of three tables is not changed no matter how these tables are joined. For example, you can join rows in tables A and B and then join A+B, a derived table, with rows in table C, or you can join rows in tables B and C, and then join B+C, a derived table, with rows in table A (the associative principle).

Because inner joins are commutative and associative, the optimizer can be used to select the best join plan for inner joins.
Previous << 1 .. 16 17 18 19 20 21 < 22 > 23 24 25 26 27 28 .. 241 >> Next