Derived Table Syntax
Note: Not all back ends support this syntax. For more information, see the Enterprise Access Developing Portable Applications guide, the Star User Guide, or the EDBC guides.
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.
SELECT xname FROM (SELECT yname FROM
(SELECT name FROM emp ) y (yname)) x (xname);
The "(xname)" and "(yname)" are column name overrides. Not all back ends support this syntax. Instead, uUse either the name that is generated from the inner select or use regular aliased column syntax.
SELECT xname FROM (SELECT yname as xname FROM
(SELECT name as yname FROM emp ) y) x;
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
Last modified date: 04/03/2024