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 .. 100 101 102 103 104 105 < 106 > 107 108 109 110 111 112 .. 241 >> Next


To select the name, project, and the number of hours spent by Example I: UNION employees assigned to project OE1-0001, plus the names of

employees not assigned to a project, the following query could be used:

SELECT Name, Proj_Id, Hours FROM Employee,Charges

WHERE Employee.Empno = Charges.Empno AND Proj_Id IN ('OE1-0001')

UNION

SELECT Name, NULL (CHAR (8)), NULL (DECIMAL (4,2))

FROM Employee

WHERE Empno NOT IN (SELECT Empno FROM Charges)

7-50

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

UNION Operator

This query returns the following rows:

Project

Name_____________Id Hours

Aguilar J ? ?
Brangle B ? ?
Chin M ? ?
Clements D ? ?
Kemper R ? ?
Marston A ? ?
Phan A ? ?
Regan R ? ?
Russell S ? ?
Smith T ? ?
Watson L ? ?
Inglis C OE1-0001 30.0
Inglis C OE1-0001 30.5
Leidner P OE1-0001 10.5
Leidner P OE1-0001 23.0
Moffit H OE1-0001 12.0
Moffit H OE1-0001 33.5

In this example, null expressions are used in columns 2 and 3 of the second SELECT statement. The null expressions are used as place markers so that both SELECT statements in the query contain the same number of expressions.

To determine the department number and names of all employees Example 2: UN1ON in departments 500 and 600, the UNION operator could be used as

follows:

SELECT DeptNo, Name FROM Employee WHERE DeptNo = 500 UNION

SELECT DeptNo, Name FROM Employee WHERE DeptNo = 6 00 ;

This query returns the following rows:

DeptNo Name
500 Carter J
500 Inglis C
500 Marston A
500 Omura H
500 Reed C
500 Smith T
500 Watson L
600 Aguilar J
600 Kemper R
600 Newman P
600 Regan R

Although these results could have been achieved with a simpler query, such as the following:

SELECT Name, DeptNo FROM Employee

WHERE (DeptNo = 500) OR (DeptNo = 600);

there is an advantage to using the UNION operator instead. The advantage is that if the Deptno column is the primary index for the Employee table, using the query with the UNION operator

Teradata RDBMS for UNIX SQL Reference

7-51
Queries: The SELECT Statement

UNION Operator

Example 3: UNION

Example 4: UNION

UNION Operator and the Outer Join

guarantees that the basic selects are prime key operations. There is no guarantee that a query using the OR operation will make use of the primary index.

In addition, the UNION operator is useful if you must merge lists of values taken from two or more tables.

For example, if departments 500 and 600 had their own Employee tables, the following query could be used to select data from two different tables and merge that data into a single list:

SELECT Name, DeptNo FROM Employee_dept_5 0 0 UNION

SELECT Name, DeptNo FROM Employee_dept_6 0 0 ;

Suppose you want to know the number of man-hours charged by each employee who is working on a project. In addition, suppose you also wanted the result to include the names of employees who are not working on a project.

To do this, you would have to perform a union operation as illustrated in the following example.

SELECT Name, Proj_Id, Hours

FROM Employee, Charges

WHERE Employee.EmpNo = Charges.EmpNo

UNION

SELECT Name, Null (CHAR(8)), Null (DECIMAL(4,2)),

FROM Employee

WHERE EmpNo NOT IN

SELECT EmpNo FROM Charges)

UNION

SELECT Null (VARCHAR(12)), Proj_Id, Hours

FROM Charges

WHERE EmpNo NOT IN

SELECT EmpNo FROM Employee);

The first portion of the statement joins the Employee table with the Charges table on the EmpNo column. The second portion accounts for the employees who may be listed in the Employee table, but not the Charges table. The third portion of the statement accounts for the employees who may be listed in the Charges table and not in the Employee table. This ensures that all the information asked for is included in the response.

Example 4 above does not illustrate an outer join. That operation returns all rows in the joined tables for which there is a match on the join condition and rows from the “left” join table, or the “right” join

7-52

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

UNION Operator

table, or both tables for which there is no match. Moreover, nonmatching rows are extended with null values.

It is possible, however, to achieve an outer join using inner joins and the UNION operator, though the union of any two inner joins is not the equivalent of an outer join.

The following example shows how to achieve an outer join using two inner joins and the UNION operator. Notice how the second inner join uses null values.

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

WHERE Offerings.CourseNo = Enrollment.CourseNo UNION

SELECT Offerings.CourseNo, Offerings.Location, NULL FROM Offerings, Enrollment

WHERE Offerings.CourseNo <> Enrollment.CourseNo;

The above UNION operation yields results equivalent to the results that the left outer join example, shown above, yields:
Previous << 1 .. 100 101 102 103 104 105 < 106 > 107 108 109 110 111 112 .. 241 >> Next