Nested table expressions(NTE) (also called derived tables)
Basic syntax of SQL SELECT statement is like
SELECT * FROM table-name
- Nested table expressions gives you option to replace "table-name"(after FROM clause) with "(SELECT-statement)" which act as Temporary table.
- To use this Temporary table in the query we must give this Temporary table a name (correlation name).
SELECT * FROM (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) AS T2
In above query "(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM CORPDATA.EMPLOYEE E1 GROUP BY WORKDEPT) AS T2" is NTE with correlation name T2.
You can use other tables with NTE in same query.
SELECT MGRNO, T1.DEPTNO, MAXSAL FROM DEPARTMENT T1, (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) T2 WHERE T1.DEPTNO = T2.WORKDEPT ORDER BY DEPTNO
In the same way above you can use multiple NTEs in a single query.
Some one can easy confuse NTE with SubQueries as both follow almost same syntax. Point to remember here is NTE replace Table in FROM clause where SubQueries give option to use a query in WHERE and HAVING clauses.