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 .. 18 19 20 21 22 23 < 24 > 25 26 27 28 29 30 .. 241 >> Next

C400 C400 Null Null
C300 Null Null Null

3-10

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

Outer Join

But if the same SELECT statement is written with a different second join conditions, as listed below, then the OFFERINGS table is full outer joined to the ENROLLMENT table, according to the first JOIN condition (Offerings.CourseNo = Enrollment.CourseNo)

SELECT Courses.CourseNo, Offerings.CourseNo,

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

ON Courses.CourseNo = Enrollment.CourseNo

The results of the query are 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 = Enrollment.CourseNo).

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

Although the two second join conditions,

Courses.CourseNo = Offerings.CourseNo

in the first example above, and

Courses.CourseNo = Enrollment.CourseNo

in the second example above, seem intuitively equivalent, the results of a select operation will be different with different join conditions.

Thus, if we use Courses.CourseNo = Offerings.CourseNo as our second join condition, we are not shown in the results of the outer join, for example, that employee 236 registered for Course C300.

But if we use Courses.CourseNo = Enrollment.CourseNo as our second join condition, we are not shown in the results of the outer join, for example, that Courses C200 and C400 are in fact offered.

Teradata RDBMS for UNIX SQL Reference

3-11
Joins: Working with Multiple Tables

Outer Join

ON Clauses and WHERE Clauses

Example

Although an ON clause (for each join operation) in a FROM clause is always required when doing an outer join, an outer join can still include a WHERE clause.

But any restriction in the WHERE clause will only be applied to the table that is the final result of the outer join. The WHERE clause does not define the join condition of the outer join.

The following SELECT statement yields the results in the table that follows before the location restriction is applied.

SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings LEFT JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNo WHERE Location = 'El Segundo';

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

After the restriction is applied (course C200 is given in Dayton), the results are as follows:

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

We can put the location restriction in the ON clause:

SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings LEFT JOIN Enrollment ON (Location = 'El Segundo') and (Offerings.CourseNo = Enrollment.CourseNo);

Such a restriction is not, strictly speaking, a join condition. It is rather a search condition.

The location restriction would, of course, be applied as part of the inner join (of the lest outer join) rather than after the left outer join has been performed.

3-12

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

Outer Join

Using Join Conditions in ON Clauses, Not Search Conditions

Example

But this may result in confusing results, as follows:

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

Although it was applied as part of the inner join (of the left outer join), the location restriction did not eliminate course C200, given in Dayton, from being returned. C200 was returned as part of the rows that were not returned as a result of the inner join.

To avoid getting unexpected results, it is sometimes recommended to use only ON clauses that reference columns from two tables that are to be joined, that is, use only join conditions in ON clauses, and not search conditions. See also the following section “Where to Put Search Conditions?”.

Consider the following two tables, A and B.

A.a A.b B.a

3 1
6 6

The following SELECT statement yields these results. The left outer join returns the data in the table that follows.

SELECT *

FROM A LEFT OUTER JOIN B ON A.a = B.a WHERE A.b > 5;

A.a A.b B.a

3 1
6 6

When the WHERE restriction is applied, the results are: A.a A.b B.a

6 6 6

But if we include the WHERE restriction as part of the join condition:

SELECT *

FROM A LEFT OUTER JOIN B ON (A.b >5) and (A.a = B.a);

Teradata RDBMS for UNIX SQL Reference

3-13
Joins: Working with Multiple Tables

Outer Join

Using Search Conditions in ON Clauses

Where to Put Search Conditions?

Joins on Views

Joins on Views Containing an Aggregate

we get some confusing results: A.a A.b

B.a

6 6
3 1

Null

6

Notice that the join condition, which specified the inner join (of the left outer join), restricts the results to 6, 6, 6. But then the rows that were not returned as a result of the inner join are returned and extended with nulls. Thus, for our second row, we get 3, 1, null.
Previous << 1 .. 18 19 20 21 22 23 < 24 > 25 26 27 28 29 30 .. 241 >> Next