SQL Getting Started

Table Expressions

If you just need the definition of a view for a single query, you can use a table expression.

Table expressions are temporary and are only valid for the life of the SQL statement; they cannot be shared, but they allow more flexibility than views. View definitions can be shared by any authorized user.

This section describes how to use common table expressions and nested table expressions in queries.

Nested Table Expressions

A nested table expression is a temporary view where the definition is nested (defined directly) in the FROM clause of the main query.

The following query uses a nested table expression to find the average total pay, education level and year of hire, for those with an education level greater than 16:

 
     SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY), 7,2)
        FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
                     SALARY+BONUS+COMM AS TOTAL_PAY
                 FROM EMPLOYEE
                 WHERE EDLEVEL > 16 )  AS PAY_LEVEL
        GROUP BY EDLEVEL, HIREYEAR
        ORDER BY EDLEVEL, HIREYEAR
 

The result is as follows:

     EDLEVEL HIREYEAR    3        
     ------- ----------- ---------
          17        1967  28850.00
          17        1973  23547.00
          17        1977  24430.00
          17        1979  25896.50
          18        1965  57970.00
          18        1968  32827.00
          18        1973  45350.00
          18        1976  31294.00
          19        1958  51120.00
          20        1975  42110.00

This query uses a nested table expression to first extract the year of hire from the HIREDATE column so that it can subsequently be used in the GROUP BY clause. You may not want to create this as a view, because you intend to perform similar queries using different values for EDLEVEL.

The scalar built-in function DECIMAL is used in this example. DECIMAL returns a decimal representation of a number or a character string. For more details on functions refer to the SQL Reference.

Common Table Expressions

A common table expression is a named result table that is defined using the WITH keyword prior to the beginning of a fullselect. It is a table expression that you create to use throughout a complex query. Define and name it at the start of the query using a WITH clause. Repeated references to a common table expression use the same result set. By comparison, if you used nested table expressions or views, the result set would be regenerated each time, with possibly different results.

The following example lists all the people in the company who have an education level greater than 16, who make less pay on average than those people who were hired at the same time and who have the same education. The parts of the query are described in further detail following the query.

(1)

     WITH
           PAYLEVEL AS                                                 
              (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,           
                      SALARY+BONUS+COMM AS TOTAL_PAY                                     
                  FROM EMPLOYEE                                                         
                  WHERE EDLEVEL > 16),                                                       

(2)

           PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS         
              (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)                  
                  FROM PAYLEVEL                                            
                  GROUP BY EDLEVEL, HIREYEAR)                                                         

(3)

     SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)  
        FROM PAYLEVEL, PAYBYED                                             
        WHERE EDLEVEL=EDUC_LEVEL                                        
          AND HIREYEAR = YEAR_OF_HIRE                              
          AND TOTAL_PAY < AVG_TOTAL_PAY                        

(1)

This is a common table expression with the name PAYLEVEL. This result table includes the year that a person was hired, the total pay for that employee, and his or her education level. Only rows for employees with an education level greater than 16 are included.

(2)

This is a common table expression with the name PAYBYED (or PAY by education). It uses the PAYLEVEL table that was created in the previous common table expression to determine the education level, hire year, and average pay of employees within each education level, hired in the same year. The columns returned by this table have been given different names (EDUC_LEVEL, for example) from the column names used in the select list. This produces a result set named PAYBYED that is the same as the result produced in the nested table expression example.

(3)

Finally, we get to the actual query that produces the desired result. The two tables (PAYLEVEL, PAYBYED) are joined to determine those individuals who have total pay that is less than the average pay for people hired in the same year. Note that PAYBYED is based on PAYLEVEL. So PAYLEVEL is effectively accessed twice in the complete statement. Both times the same set of rows are used in evaluating the query.

The final result is as follows:

     EMPNO  EDLEVEL YEAR_OF_HIRE TOTAL_PAY     5        
     ------ ------- ------------ ------------- ---------
     000210      17         1979      20132.00  25896.50
     

[ DB2 List of Books | Search the DB2 Books ]