Derived tables

A derived table is defined by the evaluation of a query expression and differs from a regular table in that it is neither described in system catalogs nor stored on disk. In Adaptive Server, a derived table may be a SQL derived table or an abstract plan derived table.
A SQL derived table: defined by one or more tables through the evaluation of a query expression. A SQL derived table is used in the query expression in which it is defined and exists only for the duration of the query.

How SQL derived tables work:
A SQL derived table is created with a derived table expression consisting of a nested select statement, as in the following example, which returns a list of cities in the publishers table of the pubs2 database:

select city from (select city from publishers)
cities

The SQL derived table is named cities and has one column titled city. The SQL derived table is defined by the nested select statement and persists only for the duration of the query, which returns the following:

Advantages of SQL derived table:

  • The SQL derived table persists only for the duration of the query, in contrast with a temporary table, which exists for the entire session.
  • SQL derived tables eliminate overhead of administrative tasks by enabling queries to spontaneously create nonpersistent tables without needing to drop the tables or make insertions into the system catalog. Consequently, no administrative tasks are required unlike a creating a view (which requires both create and drop statements in addition to a select statement. for same query).
    Note: If the query results are only needed once, you might use a SQL derived table instead of view.
  • A SQL derived table used multiple times performs comparably to a query using a view with a cached definition.

SQL derived tables and optimization:
Queries expressed as a single SQL statement exploit the optimizer better than queries expressed in two or more SQL statements. SQL derived tables allow you to express concisely, in a single step, what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored. For example:

select dt_1.* from
(select sum(total_sales) from titles_west group by total_sales)
dt_1(sales_sum),
(select sum(total_sales) from titles_east group by total_sales)
dt_2(sales_sum)
where dt_1.sales_sum = dt_2.sales_sum

Aggregate results are obtained from the SQL derived tables dt_1 and dt_2, and a join is computed between the two SQL derived tables. Everything is accomplished in a single SQL statement.

SQL derived table syntax:
The query expression for a SQL derived table is specified in the from clause of the select or select into command in place of a table or view name.
A derived table expression is similar to the select in a create view statement and follows the same rules, with the following exceptions:

  • Temporary tables are permitted in a derived table expression except when it is part of a create view statement.
  • A local variable is permitted in a derived table expression except when it is part of a create view statement. You cannot assign a value to a variable within a derived table expression.
  • A correlation_name, which must follow the derived table expression to specify the name of the SQL derived table, may omit a derived column list, whereas a view cannot have unnamed columns:

select * from (select sum(advance) from total_sales) dt

Derived column lists:
If a derived column list is not included in a SQL derived table, the names of the SQL derived table columns must match the names of the columns specified in the target list of the derived table expression.

Incorrect: column name current_date(column name specified in the target list) does not match with column name date (column specified in the query)

SELECT emp_id, current_date FROM (SELECT e.emp_id, getdate() date FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp
Error: Error (11751) The column ‘current_date’ in the outer query is not present in the derived table expression.

Correct:  column name date (column specified in query) matches with column name date (column specified in the target list of the derived table expression)

SELECT emp_id, date FROM (SELECT e.emp_id, getdate() date FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp

If a column name is not specified in the target list of the derived table expression, as in the case where a constant expression or an aggregate is present in the target list of the derived table expression, the resulting column in the SQL derived table has no name and The server returns error 11073, A derived-table expression may not have null column names…
Example:
Incorrect: getdate() is a date function and doesn’t has any column name specified in below query.
SELECT * FROM (SELECT e.emp_id, getdate() FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp
Error: Error (11073) A derived table expression may not have null column names. Use a derived column list in the derived table definition or name the column expressions in the SELECT target list.

Correct: getdate() is a date function and has a column name specified as date in below query.
SELECT * FROM (SELECT e.emp_id, getdate() date FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp

If a derived column list is included in a SQL derived table, it must specify names for all columns in the target list of the derived table expression. These column names must be used in the query block in place of the natural column names of the SQL derived table. The columns must be listed in the order in which they occur in the derived table expression, and a column name cannot be specified more than once in the derived column list. 

Example:
Incorrect: derived columns list employee_name, employee_dept included in the query but target list contains columns names as emp_id, emp_dept.
SELECT emp_id, emp_dept FROM (SELECT e.emp_id, emp_dept  FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp (employee_name, employee_dept)
Error:
Error (207) Invalid column name ‘emp_dept’.
Error (207) Invalid column name ‘emp_id’.

Correct: derived columns list employee_name, employee_dept included in the query and target list contains same columns names as derived columns list names.

SELECT employee_name, employee_dept FROM (SELECT e.emp_id, emp_dept  FROM employee e, emp_dept d WHERE e.emp_id = d.emp_id) dt_emp (employee_name, employee_dept)

 

Correlated SQL derived tables:
Correlated SQL derived tables, which are not ANSI standard, are not supported. For example, the following query is not supported because it references the SQL derived table dt_publishers2 inside the derived table expression for dt_publishers1:

select * from
(select * from titles where titles.pub_id =dt_publishers2.pub_id)
dt_publishers1,
(select * from publishers where city = “Boston”)
dt_publishers2
where dt_publishers1.pub_id = dt_publishers2.pub_id

Similarly, the following query is not supported because the derived table expression for dt_publishers references the publishers_pub_id column, which is outside the scope of the SQL derived table:

select * from publishers
        where pub_id in (select pub_id from
                                     (select pub_id from titles
where pub_id = publishers.pub_id)
                                            dt_publishers)

The following query illustrates proper referencing and is supported:
select * from publishers
where pub_id in (select pub_id from
(select pub_id from titles)
dt_publishers
 where pub_id = publishers.pub_id)

Use of SQL derived tables:
Queries expressed as a single SQL statement exploit the query processor better than queries expressed in two or more SQL statements. SQL-derived tables enable you to express, in a single step, what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored.

For example:
select dt_1.* from
(select sum(total_sales) from titles_west group by total_sales)
dt_1(sales_sum),
(select sum(total_sales) from titles_east group by total_sales)
dt_2(sales_sum)
where dt_1.sales_sum = dt_2.sales_sum

Here, aggregate results are obtained from the SQL derived tables dt_1 and dt_2, and a join is computed between the two SQL derived tables. Everything is accomplished in a single SQL statement.

Using SQL derived tables:|
You can use SQL derived tables to form part of a larger integrated query using assorted SQL clauses and operators.
Nesting:  A query can use numerous nested derived table expressions, which are SQL expressions that define a SQL derived table.
Example:
select postalcode
from (select postalcode
 from (select postalcode from authors)  dt_1)  dt_2

Note: The degree of nesting is limited to 25.

Subqueries using SQL derived tables: You can use a SQL derived table in a subquery from clause
Example:
select pub_name from publishers
where “business” in
(select type from
(select type from titles, publishers
where titles.pub_id = publishers.pub_id)
dt_titles)

A union clause is allowed within a derived table expression:
Example:
select * from
(select stor_id, ord_num from sales
union
select stor_id, ord_num from sales_east)
dt_sales_info

Unions in subqueries: A union clause is allowed in a subquery inside a derived table expression.
select title_id from salesdetail
where stor_id in
(select stor_id from (select stor_id from sales
union
select stor_id from sales_east)
dt_stores)

Renaming columns with SQL derived tables: If a derived column list is included for a SQL derived table, it follows the name of the SQL derived table and is enclosed in parentheses

Example:
select dt_b.book_title, dt_b.tot_sales
from   (select title, total_sales
 from titles) dt_b (book_title, tot_sales)
where dt_b.book_title like “%Computer%”

Here the column names title and total_sales in the derived table expression are respectively renamed to book_title and tot_sales using the derived column list. The book_title and tot_sales column names are used in the rest of the query.

Note: SQL derived tables cannot have unnamed columns.

Constant expressions:
You can specify column names for the target list of a derived table expression using a derived column list:
select * from
(select title_id, (lorange + hirange)/2
from roysched) as dt_avg_range (title, avg_range)
go
Alternately, you can specify column names by renaming the column in the target list of the derived table expression:
select * from
(select title_id, (lorange + hirange)/2 avg_range
from roysched) as dt_avg_range
go

Note: If you specify column names in both a derived column list and in the target list of the derived table expression, the resulting columns are named by the derived column list. The column names in a derived column list take precedence over the names specified in the target list of the derived table expression.

Note: If you use a constant expression within a create view statement, you must specify a column name for the constant expression results.

Aggregate functions: Derived table expressions may use aggregate functions, such as sum, avg, max, min, count_big, and count.

Example:
select dt_a.pub_id, dt_a.adv_sum
from (select pub_id, sum(advance) adv_sum
from titles group by pub_id) dt_a

Joins with SQL derived tables:
Example: Between a SQL derived table and an existing table.
select dt_c.title_id, dt_c.pub_id
from (select title_id, pub_id from titles) as dt_c,
       publishers
where dt_c.pub_id = publishers.pub_id

Example: Between two SQL derived tables.
select dt_c.title_id, dt_c.pub_id
from (select title_id, pub_id from titles)
as dt_c,
(select pub_id from publishers)
as dt_d
where dt_c.pub_id = dt_d.pub_id
Example: Outer joins involving SQL derived tables are also possible.
select dt_c.title_id, dt_c.pub_id
from (select title_id, pub_id from titles)
as dt_c,
(select title_id, pub_id from publishers)
as dt_d
where dt_c.title_id *= dt_d.title_id

Note:
  Sybase supports both left and right outer joins.

Creating a table from a SQL derived table:
Example:
select pubdate into pub_dates
from (select pubdate from titles) dt_e
where pubdate = “450128 12:30:1PM”

Here, data from the SQL derived table dt_e is inserted into the new table pub_dates.

Using views with SQL derived tables:
create view view_colo_publishers (Pub_Id, Publisher,
City, State)
as select pub_id, pub_name, city, state from
(select * from publishers where state=”CO”)
dt_colo_pubs

Data can be inserted through a view that contains a SQL derived table if the insert rules and permission settings for the derived table expression follow the insert rules and permission settings for the select part of the create view statement. For example, the following insert statement inserts a row through the view_colo_publishers view into the publishers table on which the view is based:

insert view_colo_publishers values (‘1799′, ‘Gigantico Publications’, ‘Denver’, ‘CO’)

You can also update existing data through a view that uses a SQL derived table:

update view_colo_publishers set Publisher = “Colossicorp Industries”
where Pub_Id = 1699

Note: You must specify the column names of the view definition, not the column names of the underlying table.

Correlated attributes: Correlated attributes that exceed the scope of a SQL derived table cannot be referenced from a SQL derived table expression

An abstract plan derived table: a derived table used in query processing, the optimization and execution of queries. An abstract plan derived table differs from a SQL derived table in that it exists as part of an abstract plan and is invisible to the end user.

This entry was posted in RDBMS concepts, Sybase ASE and tagged . Bookmark the permalink.

Leave a Reply