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