Subqueries in the FROM Clause (Derived Tables)
Derived tables let you create or simplify complex queries. Useful in data warehousing applications, they provide a way to isolate complex portions of query syntax from the rest of a query.
A derived table is the coding of a SELECT in the FROM clause of a SELECT statement. The derived table must always use a correlation name.
For example:
SELECT relid, x.attname
FROM (SELECT attrelid, attrelidx, attname,
attfrml FROM iiattribute) x, iirelation
WHERE reltid = attrelid AND reltidx = x.attrelidx
The derived table behaves like an inline view; the rows of the result set of the derived table are read and joined to the rest of the query. If possible, the derived table is flattened into the containing query to permit the query compiler to better optimize the query as a whole.
Some complex queries cannot be implemented without using either pre-defined views or derived tables. The derived table approach is more concise than pre-defined views, and avoids having to define persistent objects, such as views, that may be used for a single query only.
For example, consider a query that joins information to some aggregate expressions. Derived tables allow the aggregates to be defined and joined to non-aggregated rows of some other tables all in the same query. Without derived tables, a persistent view would have to be defined to compute the aggregates. Then a query would have to be coded to join the aggregate view to the non-aggregated data.
Derived Table Syntax
The SELECT in the FROM clause must be enclosed in parentheses and must include a correlation name.
Following the correlation name, the derived table can include an override list of column names in parentheses, or these column names can be coded with AS clauses in the SELECT list of the derived table.
Columns in the derived table can be referenced in SELECT, ON, WHERE, GROUP BY, and HAVING clauses of the containing query, qualified by the correlation name, if necessary.
Example Queries Using Derived Tables
SELECT e.ename FROM employee e,
(SELECT AVG(e1.salary), e1.dno FROM employee e1
GROUP BY e1.dno) e2 (avgsal, dno)
WHERE e.dno = e2.dno AND e.salary > e2.avgsal
Changing columns names with AS clause:
SELECT e.ename FROM employee e,
(SELECT AVG(e1.salary) AS avgsal, e1.dno FROM employee e1
GROUP BY e1.dno) e2
WHERE e.dno = e2.dno AND e.salary > e2.avgsal
Example Query Using Derived Tables and Scalar Subquery
SELECT relid, x.attname
FROM (SELECT attrelid, attrelidx, attname,attfrml
FROM attribute WHERE attrelid=(SELECT MAX(reltid) FROM relation)) x, relation
WHERE reltid = attrelid AND reltidx = x.attrelidx