Using table expressions

Table expressions can be used in place of a view to avoid creating the view when general use of the view is not required. Table expressions consist of nested table expressions (also called derived tables) and common table expressions.

Nested table expressions are specified within parentheses in the FROM clause. For example, suppose you want a result table that shows the manager number, department number, and maximum salary for each department. The manager number is in the DEPARTMENT table, the department number is in both the DEPARTMENT and EMPLOYEE tables, and the salaries are in the EMPLOYEE table. You can use a table expression in the FROM clause to select the maximum salary for each department. You can also add a correlation name, T2, following the nested table expression to name the derived table. The outer select then uses T2 to qualify columns that are selected from the derived table, in this case MAXSAL and WORKDEPT. Note that the MAX(SALARY) column selected in the nested table expression must be named in order to be referenced in the outer select. The AS clause is used to do that.

 SELECT MGRNO, T1.DEPTNO, MAXSAL
 FROM CORPDATA.DEPARTMENT T1,
       (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT
        FROM CORPDATA.EMPLOYEE E1
        GROUP BY WORKDEPT) T2
 WHERE T1.DEPTNO = T2.WORKDEPT
 ORDER BY DEPTNO

The result of the query follows.

Common table expressions can be specified before the full-select in a SELECT statement, an INSERT statement, or a CREATE VIEW statement. They can be used when the same result table needs to be shared in a full-select. Common table expressions are preceded with the keyword WITH.

For example, suppose you want a table that shows the minimum and maximum of the average salary of a certain set of departments. The first character of the department number has some meaning and you want to get the minimum and maximum for those departments that start with the letter 'D' and those that start with the letter 'E'. You can use a common table expression to select the average salary for each department. Again, you must name the derived table; in this case, the name is DT. You can then specify a SELECT statement using a WHERE clause to restrict the selection to only the departments that begin with a certain letter. Specify the minimum and maximum of column AVGSAL from the derived table DT. Specify a UNION to get the results for the letter 'E' and the results for the letter 'D'.

WITH DT AS (SELECT E.WORKDEPT AS DEPTNO, AVG(SALARY) AS AVGSAL
            FROM CORPDATA.DEPARTMENT D , CORPDATA.EMPLOYEE E
            WHERE D.DEPTNO = E.WORKDEPT
            GROUP BY E.WORKDEPT)
 SELECT 'E', MAX(AVGSAL), MIN(AVGSAL) FROM DT
 WHERE DEPTNO LIKE 'E%'
 UNION
 SELECT 'D', MAX(AVGSAL), MIN(AVGSAL) FROM DT
 WHERE DEPTNO LIKE 'D%'

The result of the query follows.

Suppose that you want to write a query against your ordering database that will return the top 5 items (in total quantity ordered) within the last 1000 orders from customers who also ordered item 'XXX'.

WITH X AS (SELECT ORDER_ID, CUST_ID
                  FROM ORDERS
                  ORDER BY ORD_DATE DESC
                  FETCH FIRST 1000 ROWS ONLY),
     Y AS (SELECT CUST_ID, LINE_ID, ORDER_QTY
                  FROM X, ORDERLINE
                  WHERE X.ORDER_ID = ORDERLINE.ORDER_ID)
SELECT LINE_ID 
     FROM (SELECT LINE_ID
                  FROM Y
                  WHERE Y.CUST_ID IN (SELECT DISTINCT CUST_ID
                                             FROM Y
                                             WHERE LINE.ID = 'XXX' )
                  GROUP BY LINE_ID
                  ORDER BY SUM(ORDER_QTY) DESC)
   FETCH FIRST 5 ROWS ONLY

The first common table expression (X) returns the most recent 1000 order numbers. The result is ordered by the date in descending order and then only the first 1000 of those ordered rows are returned as the result table.

The second common table expression (Y) joins the most recent 1000 orders with the line item table and returns (for each of the 1000 orders) the customer, line item, and quantity of the line item for that order.

The derived table in the main select statement returns the line items for the customers who are in the top 1000 orders who ordered item XXX. The results for all customers who ordered XXX are then grouped by the line item and the groups are ordered by the total quantity of the line item.

Finally, the outer select selects only the first 5 rows from the ordered list that the derived table returned.