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 .. 91 92 93 94 95 96 < 97 > 98 99 100 101 102 103 .. 241 >> Next


7-19
Queries: The SELECT Statement

Derived Tables

Introduction

Rules for Using Derived Tables

Derived Tables

A table can be a base table, a derived table, or a view.

A derived table is obtained from one or more other tables through the results of a query.

Derived tables can do all of the following things:

• Enhance performance.

How derived tables are implemented determines how or if performance is enhanced or not. For example, one way of optimizing a query is for the user to control how data from different tables is accessed in joins, by using derived tables.

The use of a derived table in a SELECT forces the subquery to create a spool file, which then becomes the derived table. Derived tables can then be treated in the same way as base tables.

• Avoid CREATE and DROP TABLE statements for storing retrieved information.

• Assist in optimizing joins (see Chapter 3).

Note: The scope of derived tables is only visible to the level of the SELECT statement calling the subquery.

Derived tables are one of the options in the FROM list, and can also be used in the ABORT and DELETE statements.

The use of derived tables in the FROM clause is fully ANSI compliant.

Adhere to the following rules when using derived tables:

• A unique table name is required for the derived table.

• A column name list is optional if the fields in the SELECT list of the derived table are unique.

For example:

SELECT * FROM (SELECT a FROM t1) t5;

SELECT * FROM (SELECT a FROM t1) t5(x);

• A column name list is required if there is an expression in the SELECT list of the derived table.

For example:

SELECT * FROM (SELECT MAX(a) FROM t1) t5(x);

SELECT * FROM (SELECT MAX(a), a+b FROM t1) t5 (x,y);

7-20

Teradata RDBMS for UNIX SQL Reference
Derived Tables and Multilevel Aggregates

Queries: The SELECT Statement

Derived Tables

• A column name list is required if the fields in the SELECT list of the derived table are not unique.

For example:

SELECT * FROM (SELECT t1.a, t2.a FROM t1, t2) t5 (x,y);

• The number of fields in the column name list for the derived table must be the same as the number of fields in the SELECT list of the derived table.

Aggregates are not allowed in WHERE phrases. Because of this, it has been difficult to obtain values such as ‘above-average’ salary in a single query. The derived table feature makes such calculations easy to perform.

The following example shows how derived tables facilitate this:

SELECT Name, Salary, Average_Salary FROM

(SELECT AVG(Salary) FROM Employee) Workers (Average_Salary), Employee

WHERE Salary > Average_Salary ORDER BY Salary DESC;

returns:

Name_________Salary_____Average_Salary

Russel S 55,000.00 38,147.62

Watson L 56,000.00 38,147.62

Phan A 55,000.00 38,147.62

Aguilar J 45,000.00 38,147.62

Carter J 44,000.00 38,147.62

In this example, the WHERE condition compares values from rows in the base table Employee with the (in this case single) values of the field Average_Salary in the derived table, Workers.

A more elaborate example is to group the same information by DeptNo:

SELECT Name, Salary, DeptNo, Average_Salary FROM

(SELECT AVG(Salary), DeptNo FROM Employee GROUP BY DeptNo) Workers (Average_Salary, DeptNum), Employee

WHERE Salary > Average_Salary AND DeptNum = DeptNo ORDER BY DeptNo,Salary DESC;

Here the request is to return salaries above the department averages. This returns:

Name Salary DeptNo Average_Salary
Chin M 38, . 000.00 100 32, , 625.00
Moffit H 35, . 000.00 100 32, , 625.00
Russel S 55, . 000.00 300 47, ,666.67
Phan A 55, . 000.00 300 47, ,666.67
Watson L 56, . 000.00 500 38, ,285.71
Carter J 44, . 000.00 500 38, ,285.71
Smith T 42, . 000.00 500 38, ,285.71
Aguilar J 45, . 000.00 600 36, , 650.00

Teradata RDBMS for UNIX SQL Reference

7-21
Queries: The SELECT Statement

Derived Tables

Here the derived table is a grouped table, providing a set of rows. The outer WHERE clause needs an equality join between the Department Number of the base table, Employee, and the derived table.

The following illustrate the use of the FROM list in queries:

Examples

The following statement may be used to list employees who have Example 1: fROm List more years of work experience than their department managers:

SELECT Workers.Name, Workers.YrsExp, Workers.DeptNo, Managers.Name, Managers.YrsExp FROM Employee Workers, Employee Managers WHERE Managers.DeptNo = Workers.DeptNo AND

UPPER (Managers.JobTitle) IN ('MANAGER' OR 'VICE PRES') AND Workers.YrsExp > Managers.YrsExp ;

The FROM clause in the preceding statement allows the Employee table to be treated as though it were two identical tables; one named “Workers,” the other named “Managers.”

As in a normal join operation, the WHERE clause defines the conditions of the join, establishing DeptNo as the column whose values are common to both tables.

The request is processed by first selecting “Managers” rows that contain a value of either ‘MANAGER’ or ‘VICE PRES’ in the JobTitle field. These rows are then joined to the “Workers” rows using a merge-join operation with the following join condition:
Previous << 1 .. 91 92 93 94 95 96 < 97 > 98 99 100 101 102 103 .. 241 >> Next