Derived Tables Teradata

Derived tables were introduced into Teradata with V2R2. The creation of the derived table is local to the SQL statement and available only for a single request. However, a request may contain multiple derived tables. Once these tables are defined and populated, they may be joined or manipulated just like any other table. Derived tables become an alternative to creating views or the use of interim tables.

Derived tables are very useful. However, since they only exist for the duration of a single request, they may not be a practical solution if the rows are needed for multiple, follow-up queries needing the same data. The derived table is materialized in spool space, used and dropped automatically at the end of the query. Since it is entirely in spool, it only requires the user to have enough spool space. Since there is no DD involvement, special privileges are not required.

The process of deriving a table is much like deriving column data. They are both done dynamically in an SQL statement. The main difference is that column data is normally derived in the SELECT list, but derived tables are defined in the FROM. A derived table is created dynamically by referring to it in the FROM portion of a SELECT, UPDATE or DELETE. Like all tables, it needs a table name, one or more column names and data rows. All of these requirements are established in the FROM portion of an SQL statement.

The following is the syntax for creating a derived table:

In the above syntax, everything after the first FROM is used to dynamically name the derived table with its columns and populate it with a SELECT. The SELECT is in parentheses and looks like a subquery. However, subqueries are written in the WHERE clause and this is in the FROM. This SELECT is used to populate the table like an INSERT/SELECT for a real table, but without the INSERT.

The derived table and its columns must have valid names. If desired, the derived table column names can default to the actual column names in the SELECT from a real table. Otherwise, they can be alias names established using AS in the SELECT of the derived table, or specified in the parentheses after the name of the derived table, like in a CREATE VIEW. Using this technique is our preference. It makes the names easy to find because they are all physically close together and does not require a search through the entire SELECT list to find them.

These columns receive their data type from the columns listed in the SELECT from a real table. Their respective data types are established as a result of the sequence that the columns appear in the SELECT list. If a different data type is required, the CAST can be used to make the adjustment.

The following is a simple example using a derived table named DT with a column alias called avgsal and its data value is obtained using the AVG aggregation:

SELECT * FROM (SELECT AVG(salary) FROM Employee_table) DT(avgsal) ; 1 Row Returneddata value is obtained using the AVG aggregation

Once the derived table has been materialized and populated, the actual SQL statement reads its rows from the derived table, just like any other table. Although this derived table and its SELECT are simplified, it can be any valid SELECT and therefore can use any of the SQL constructs such as: inner and outer joins, one or more set operators, subqueries and correlated subqueries, aggregates and OLAP functions. Like a view, it cannot contain an ORDER BY, a WITH, or a WITH BY. However, these operations can still be requested in the main query, just not in the SELECT for the derived table.

The best thing about a derived table is that the user is not required to have CREATE TABLE privileges and after its use. A derived table is automatically "dropped" to "clean up" after itself. However, since it is dropped the data rows are not available for a second SELECT operation. When these rows are needed in more than a single SELECT, a derived table may not be as efficient as a volatile or global temporary table.

The next example uses the same derived table named DT to join against the Employee table to find all the employees who make less than the average salary:

5 Row ReturnedEmployee table to find all the employees who make less than the average salary

Now that avgsal is a column, it can be selected for display as well as being compared to determine which rows to return.

This derived table below is a bit more involved; it contains two columns and some number of rows created by doing an aggregation with a GROUP BY and then does aggregation on the aggregates in the derived table:

Both columns, Derived_Col1 and Derived_Col2, are named in parentheses following the derived table name, My_Derived_Tbl. The FROM is also used to populate My_Derived_Tbl via the SELECT using the table called Oth_Tbl. The derived table is then used to provide the aggregates of SUM and AVG on the column called Derived_Col2. The tables called Oth_Tbl and Payment_Table (later example) do not exist in my data tables. They are used here to illustrate the mechanics of more suffocated derived tables only and not executed to see the rows. Please continue reading the next paragraphs for a detail explanation regarding the usage of derived tables.

The data value for Derived_Col1 is obtained from data in the real column called OthT_Col1. It is selected as a non-aggregate and specified in the GROUP BY of the SELECT of the derived table; it follows normal aggregation rules. The derived table will contain one row for each unique value in OthT_Col1. Then, the column called Derived_Col2 is derived using the SUM aggregate on column OthT_Col2.

Therefore, the main SELECT in this query is from the derived table to perform a SUM and an AVG on the value obtained using a SUM of OthT_Col2. Like using a view, this is a fast, easy way to perform aggregation on aggregates.

This example uses a derived table to accomplish the same processing seen previously with an interim table:

3 Rows Returnedaccomplish the same processing seen previously with an interim table

The next SELECT is rather involved; it builds My_Derived_Tbl as a derived table:

The derived table is created using an INNER JOIN and a Correlated Subquery. The main SELECT then uses the derived table as the outer table to process an OUTER JOIN. It is joined with the Payment table and uses a Correlated Subquery to make sure that only the latest payment is accessed for each account.

Whether your requirements are straightforward or complex, derived tables provide an ad hoc method to create a "table" with data rows and use them one time in an SQL statement without needing a real table to store them.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics