Using table expressions

 

You can use table expressions to specify an intermediate result table.

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.

MGRNO	DEPTNO	MAXSAL
000010	A00	52750.00
000020	B01	41250.00
000030	C01	38250.00
000060	D11	32250.00
000070	D21	36170.00
000050	E01	40175.00
000090	E11	29750.00
000100	E21	26150.00

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, 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.

	MAX(AVGSAL)	MIN(AVGSAL)
E	40175.00	21020.00
D	25668.57	25147.27

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.

 

Parent topic:

Retrieving data using the SELECT statement