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 .. 15 16 17 18 19 20 < 21 > 22 23 24 25 26 27 .. 241 >> Next


Outer Join

Outer Join

Introduction

The outer join is an extension of an inner join.

All outer joins of two or more tables perform an inner join of those tables according to a join condition and also return rows from the left join table, the right join table, or both, which were not returned in the result of the inner join, extending these rows with nulls.

The following SELECT statement is an example of a left outer join of two tables, A and B.

SELECT . . .

FROM A LEFT OUTER JOIN B ON <join condition>;

The left table in the above example means the table specified to the left of the keywords OUTER JOIN in the FROM clause. The right table means the table specified to the right of the keywords OUTER JOIN.

The type of outer join (in this case, left), specified by the keyword LEFT indicates the source of the rows not returned in the result of the inner join. These are called non-matching rows. The other outer join types are right and full, specified by the key words RIGHT and FULL.

In addition to performing an inner join of two or more tables according to a join condition, a left outer join, as in the example above, returns non-matching rows from its left table (table A) and extends them with nulls.

A right outer join returns non-matching rows from its right table and extends them with nulls. A full outer join returns non-matching rows from both its tables and extends them with nulls.

The reserved word OUTER is optional so that the above SELECT statement could be written as follows:

SELECT . . .

FROM A LEFT JOIN B ON <join condition>;

ON Clause

Having a join condition specified in an ON clause within a FROM clause is always required if the keyword JOIN is specified for either an inner join or an outer join.

The Default Join

Unless specified as outer joins (left, or right, or full outer join), all joins are by default inner joins. You can, however, write an inner join using the reserved word INNER.

Teradata RDBMS for UNIX SQL Reference

3-3
Joins: Working with Multiple Tables

Outer Join

Nulls

The following SELECT statement is an inner join of two tables, A and B.

SELECT . . .

FROM A INNER JOIN B . . .

Since the keywords INNER and JOIN are optional, the following SELECT statements are also correct examples of inner joins:

SELECT . . .

FROM A JOIN B . . .

SELECT . . .

FROM A, B . . .

For inner joins, the join condition can be specified using either the ON clause or the WHERE clause. But the ON clause is required if the keyword JOIN is specified in an inner join.

The key feature of the outer join is that in returning rows from the left join table, the right join table, or both tables, the outer join extends the rows that have no matching values with nulls, as if these values came from the corresponding table.

Suppose, for example, you wish to list courses offered by customer education for which employees have registered and include in that list those courses for which no one signed up.

For our example, we can use the following two tables. The OFFERINGS table shows customer education courses currently being offered. The ENROLLMENT table shows employees who have registered for courses, some of which may not be offered. The OFFERINGS table also lists the location of courses being offered.

OFFERINGS ENROLLMENT

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

3-4

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

Outer Join

Left Outer Join

The OFFERINGS table, with respect to courses being offered, is a subset of a third table, COURSES. The COURSES table lists all courses developed by customer education, some of which are not currently being offered (e.g., C300).

COURSES

If we perform a left outer join on the OFFERINGS and ENROLLMENT tables, the rows from the left table (the table specified to the left of the keywords OUTER JOIN) which are not returned in the result of the inner join of these two tables, are returned in the outer join result and extended with nulls.

The following SELECT statement, for example, yields the results in the table that follows.

SELECT Offerings.CourseNo, Offerings.Location,

Enrollment.EmpNo

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

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

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.

Again, the use of the keyword OUTER in the FROM clause is optional so we could have written the above SELECT statement:

SELECT Offerings.CourseNo, Offerings.Location,

Enrollment.EmpNo

Previous << 1 .. 15 16 17 18 19 20 < 21 > 22 23 24 25 26 27 .. 241 >> Next