In all previous queries, the WHERE clause contained search conditions that the database manager used to choose rows for computing expressions in the select-list. A query can refer to a value or set of values computed by another query (called a subquery).
Consider this query which finds all the activities for project IF1000:
SELECT ACTNO, ACSTAFF FROM PROJ_ACT WHERE PROJNO = 'IF1000'
Suppose that you want to modify the query so it finds the activities for project IF1000 whose estimated mean number of employees is greater than the minimum estimated mean for that project.
The problem involves two queries:
|
A pseudocode solution for the problem is as follows:
 |
You can arrive at the same result by using a single query with a subquery. Subqueries must be enclosed in parentheses, and may appear in a WHERE clause or a HAVING clause. The result of the subquery is substituted directly into the outer-level predicate in which the subquery appears; thus, there must not be an INTO clause in a subquery. For example, this query solves the above problem:
 |
The example subquery above is indented for ease of reading. Remember, however, that the syntax of SQL is fully linear and no syntactic meaning is carried by indentation or by breaking a query into several lines.
By using a subquery, the pseudocode is simplified:
EXEC SQL DECLARE C1 CURSOR FOR
SELECT ACTNO, ACSTAFF
FROM PROJ_ACT
WHERE PROJNO = 'IF1000'
AND ACSTAFF >
(SELECT MIN(ACSTAFF)
FROM PROJ_ACT
WHERE PROJNO = 'IF1000')
EXEC SQL OPEN C1
EXEC SQL FETCH C1 INTO :AN, : AS
DO WHILE (SQLCODE=0)
DISPLAY (AN, AS)
EXEC SQL FETCH C1 INTO :AN, :AS
END-DO
DISPLAY ('END OF LIST')
EXEC SQL CLOSE C1
|
The subquery above returns a single value MIN(ACSTAFF) to the outer-level query. Subqueries can return either a single value, no value, or a set of values; each variation has different considerations. In any case, a subquery must have only a single column or expression in its select-list, and must not have an ORDER BY clause.
Returning a Single Value: If a subquery returns a single value, as the one subquery above did, you can use it on the right side of any predicate in the WHERE clause or HAVING clause.
Returning No Value: If a subquery returns no value (an empty set), the outer-level predicate containing the subquery evaluates to the unknown truth-value.
If a subquery returns more than one value, you must modify the comparison operators in your predicate by attaching the suffix ALL, ANY, or SOME. These suffixes determine how the set of values returned is to be treated in the outer-level predicate. The > comparison operator is used as an example (the remarks below apply to the other operators as well):
The following example uses a > ALL comparison to find those projects with activities whose estimated mean number of employees is greater than all of the corresponding numbers for project AD3111:
DECLARE C1 CURSOR FOR
SELECT PROJNO, ACTNO
FROM PROJ_ACT
WHERE ACSTAFF > ALL
(SELECT ACSTAFF
FROM PROJ_ACT
WHERE PROJNO = 'AD3111')
OPEN C1
FETCH C1 INTO :PN, :AN
CLOSE C1
|
Your query can also use the operators IN and NOT IN when a subquery returns a set of values. For example, the following query lists the surnames of employees responsible for projects MA2100 and OP2012:
DECLARE C1 CURSOR FOR
SELECT LASTNAME
FROM EMPLOYEE
WHERE EMPNO IN
(SELECT RESPEMP
FROM PROJECT
WHERE PROJNO = 'MA2100'
OR PROJNO = 'OP2012')
OPEN C1
FETCH C1 INTO :LNAME
CLOSE C1
|
The subquery is evaluated once, and the resulting list is substituted directly into the outer-level query. For example, if the subquery above selects employee numbers 60 and 330, the outer-level query is evaluated as if its WHERE clause were:
WHERE EMPNO IN (60, 330)
The list of values returned by the subquery can contain zero, one, or more values. The operator IN is equivalent to =ANY, and NOT IN is equivalent to <>ALL.
A subquery can contain GROUP BY or HAVING clauses. If it is linked by an unmodified comparison operator such as = or >, the subquery may return one group. If it is linked by a modified comparison operator ALL, ANY, or SOME, [NOT] IN, or [NOT] EXISTS , it may return more than one group.
A subquery may include a join, a grouping, or one or more inner-level subqueries. You may include many subqueries in the same outer-level query, each in its own predicate and enclosed in parentheses.
In all the examples of subqueries above, the subquery is evaluated only once and the resulting value or set of values is substituted into the outer-level predicate. For example, recall this query from the previous section:
DECLARE C1 CURSOR FOR
SELECT ACTNO, ACSTAFF
FROM PROJ_ACT
WHERE PROJNO = 'IF1000'
AND ACSTAFF >
(SELECT MIN(ACSTAFF)
FROM PROJ_ACT
WHERE PROJNO = 'IF1000')
|
This query finds the activities for project IF1000 whose estimated mean number of employees is greater than the minimum estimated mean for that project. Now consider the following problem:
Find the project and activity numbers for activities that have an estimated mean number of employees that is less than the average estimated mean for that activity as calculated across all projects.
The subquery needs to be evaluated once for every activity number. You can do this by using the correlation capability of SQL, which permits you to write a subquery that is executed repeatedly, once for each row of the table identified in the outer-level query. This type of "correlated subquery" computes some property of each row of the outer-level table that is needed to evaluate a predicate in the subquery.
In the first query, the subquery was evaluated once for a particular project; in the new problem, it must be evaluated once for every activity. One way to solve the problem is to place the query in a cursor definition and open the cursor once for each different activity. The activities are determined by using a separate cursor.
Here is a pseudocode solution:
By using a correlated subquery, you can let the system do the work for you and reduce the amount of code you need to write.
To write a query with a correlated subquery, you use the same basic format as an ordinary outer query with a subquery.
However, in the FROM clause of the outer query, just after the table name, you place a correlation_name. (See Joining a Table to Itself Using a Correlation Name for more information on correlation names.) The subquery may then contain column references qualified by the correlation_name. For example, if X is a correlation_name, then "X.ACTNO" means "the ACTNO value of the current row of the table in the outer query." The subquery is (conceptually) reevaluated for each row of the table in the outer query.
The following query solves the problem presented earlier. That is, it finds the project and activity numbers for activities that have an estimated mean number of employees that is less than the average estimated mean for that activity, as calculated across all projects.
SELECT PROJNO,ACTNO,ACSTAFF
FROM PROJ_ACT X
WHERE ACSTAFF < (SELECT AVG(ACSTAFF)
FROM PROJ_ACT
WHERE ACTNO = X.ACTNO)
The pseudocode for the correlated subquery solution is:
EXEC SQL DECLARE QUERY CURSOR FOR
SELECT PROJNO,ACTNO,ACSTAFF
FROM PROJ_ACT X
WHERE ACSTAFF < (SELECT AVG(ACSTAFF)
FROM PROJ_ACT
WHERE ACTNO = X.ACTNO)
EXEC SQL OPEN QUERY
EXEC SQL FETCH QUERY INTO :PROJNO, :ACTNO, :ACSTAFF
DO WHILE (SQLCODE=0)
DISPLAY (PROJNO, ACTNO, ACSTAFF)
EXEC SQL FETCH QUERY INTO :PROJNO, :ACTNO, :ACSTAFF
END-DO
DISPLAY ('END OF LIST')
EXEC SQL CLOSE QUERY
|
Conceptually, the query is evaluated as follows:
 |
| Note: | ACTNO = X.ACTNO is not used in the WHERE clause of the outer-level query as it was in the uncorrelated subquery, because the system keeps track of the X.ACTNO for which it is evaluating the query. |
Suppose another condition is added to the problem:
Find the project and activity numbers |for activities that have an estimated end date after January 1, |2000 and have an estimated mean number of employees that is less than the average estimated mean for that activity.
The new query is:
EXEC SQL DECLARE QUERY CURSOR FOR
SELECT PROJNO, ACTNO, ACSTAFF
FROM PROJ_ACT X
WHERE ACENDATE > '2000-01-01'
AND ACSTAFF <
(SELECT AVG(ACSTAFF)
FROM PROJ_ACT
WHERE ACTNO = X.ACTNO)
EXEC SQL OPEN QUERY
EXEC SQL FETCH QUERY INTO :PN, :AN., :AS
EXEC SQL CLOSE QUERY
|
The X table in this query is slightly different. Conceptually, whenever there are other conditions besides the one containing the subquery, they are applied to the correlation table first. The X table that is derived from the PROJ_ACT table is:
PROJNO ACTNO ACSTAFF ACSTDATE ACENDATE ------ ------ ------- ---------- ---------- AD3111 80 1.25 1999-04-15 2000-01-15 MA2112 70 1.50 1999-02-15 2000-02-01 MA2113 70 2.00 1999-04-01 2000-12-15 MA2113 80 1.50 1999-09-01 2000-02-01 OP1010 130 4.00 1999-01-01 2000-02-01 Only rows with an ACENDATE greater than '2000-01-01' are included in this "correlation table". |
The values 70, 80, and 130 are used for X.ACTNO. Similarly, if you include a GROUP BY clause in the outer-level query, that grouping is applied to the conceptual correlation table first. Thus, if you use a correlated subquery in a HAVING clause, it is evaluated once per group of the conceptual table (as defined by the outer-level query's GROUP BY clause). When you use a correlated subquery in a HAVING clause, the correlated column-reference in the subquery must be a property of each group (that is, must be either the group-identifying column or another column used with a column function).
The use of a column function with a correlated reference in a subquery is called a correlated function. The argument of a correlated function must be exactly one correlated column (for example, X.ACSTAFF), not an expression. A correlated function may specify the DISTINCT option; for example: COUNT(DISTINCT X.ACTNO). If so, the DISTINCT counts as the single permitted DISTINCT specification for the outer-level query-block (remember that each query-block may use DISTINCT only once). For information on query-block, refer to the DB2 Server for VSE & VM Database Administration manual.
When would you want to use a correlated subquery? The use of a column function is sometimes a clue. Consider this problem:
List the employees whose level of education is higher than the average for their department.
First you must determine the select-list items. The problem says to "List the employees". This implies that the query should return something to identify the employees. LASTNAME from the EMPLOYEE table should be sufficient. The problem also discusses the level of education (EDLEVEL) and the employees' departments (WORKDEPT). While the problem does not explicitly ask for these columns, including them in the select-list will help illustrate the solution. A part of the query can now be constructed:
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE
Next, a search condition (WHERE clause) is needed. The problem statement says, "...whose level of education is higher than the average for that employee's department". This means that for every employee in the table, the average education level for that employee's department must be computed. This statement fits the description of a correlated subquery. Some property (average level of education of the current employee's department) is being computed for each row. A correlation_name is needed on the EMPLOYEE table:
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE Y
The subquery needed is simple; it computes the average level of education for each department:
 |
The complete SQL statement is:
SELECT LASTNAME, WORKDEPT, EDLEVEL
FROM EMPLOYEE Y
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT)
This will produce the following:
LASTNAME WORKDEPT EDLEVEL
--------------- -------- -------
HAAS A00 18
KWAN C01 20
PULASKI D21 16
HENDERSON E11 16
LUCCHESI A00 19
PIANKA D11 17
SCOUTTEN D11 17
JONES D11 17
LUTZ D11 18
MARINO D21 17
JOHNSON D21 16
SCHNEIDER E11 17
MEHTA E21 16
GOUNOT E21 16
|
Suppose that instead of listing the employee's department number, you list the department name. A glance at the sample tables will tell you that the information you need (DEPTNAME) is in a separate table (DEPARTMENT). The outer-level query that defines a correlation variable can also be a join query.
When you use joins in an outer-level query, list the tables to be joined in the FROM clause, and place the correlation_name next to one of these table names.
To modify the query to list the department's name instead of the number, replace WORKDEPT by DEPTNAME in the select-list. The FROM clause must now also include the DEPARTMENT table, and the WHERE clause must express the appropriate join condition.
This is the modified query:
SELECT LASTNAME, DEPTNAME, EDLEVEL
FROM EMPLOYEE Y, DEPARTMENT
WHERE Y.WORKDEPT = DEPARTMENT.DEPTNO
AND EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT)
|
This will produce the following:
LASTNAME DEPTNAME EDLEVEL --------------- ------------------------------------ ------- HAAS SPIFFY COMPUTER SERVICE DIV. 18 LUCCHESI SPIFFY COMPUTER SERVICE DIV. 19 KWAN INFORMATION CENTER 20 PIANKA MANUFACTURING SYSTEMS 17 SCOUTTEN MANUFACTURING SYSTEMS 17 JONES MANUFACTURING SYSTEMS 17 LUTZ MANUFACTURING SYSTEMS 18 PULASKI ADMINISTRATION SYSTEMS 16 MARINO ADMINISTRATION SYSTEMS 17 JOHNSON ADMINISTRATION SYSTEMS 16 HENDERSON OPERATIONS 16 SCHNEIDER OPERATIONS 17 MEHTA SOFTWARE SUPPORT 16 GOUNOT SOFTWARE SUPPORT 16 |
The above examples show that the correlation_name used in a subquery must be defined in the FROM clause of some query that contains the correlated subquery. However, this containment may involve several levels of nesting. Suppose that some departments have only a few employees and therefore their average education level may be misleading. You might decide that in order for the average level of education to be a meaningful number to compare an employee against, there must be at least five employees in a department. The new statement of the problem is:
List the employees whose level of education is higher than the average for that employee's department. Only consider departments with at least five employees.
The problem implies another subquery because, for each employee in the outer-level query, the total number of employees in that persons department must be counted:
SELECT COUNT(*) FROM EMPLOYEE WHERE WORKDEPT = Y.WORKDEPT
Only if the count is greater than or equal to 5 is an average to be computed:
SELECT AVG(EDLEVEL)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT
AND 5 <=
(SELECT COUNT(*)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT)
Finally, only those employees whose level of education is greater than the average for that department are included:
SELECT LASTNAME, DEPTNAME, EDLEVEL
FROM EMPLOYEE Y, DEPARTMENT
WHERE Y.WORKDEPT = DEPARTMENT.DEPTNO
AND EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT
AND 5 <=
(SELECT COUNT(*)
FROM EMPLOYEE
WHERE WORKDEPT = Y.WORKDEPT));
|
This will produce the following:
LASTNAME DEPTNAME EDLEVEL --------------- ------------------------------------ ------- PIANKA MANUFACTURING SYSTEMS 17 SCOUTTEN MANUFACTURING SYSTEMS 17 JONES MANUFACTURING SYSTEMS 17 LUTZ MANUFACTURING SYSTEMS 18 PULASKI ADMINISTRATION SYSTEMS 16 MARINO ADMINISTRATION SYSTEMS 17 JOHNSON ADMINISTRATION SYSTEMS 16 HENDERSON OPERATIONS 16 SCHNEIDER OPERATIONS 17 |
| Note: | The above query is different from the previous correlated subqueries in that the first subquery may return no values. Suppose that a department with three employees is being evaluated. |
Working from bottom to top, the following occurs:
 |
The inner-most subquery evaluates to 3. Thus, the expression "AND 5 <= 3" is false. Because that expression is false, no rows satisfy the search condition of the next subquery, and a null value is returned to the outer-most query. This causes the predicate "EDLEVEL > subquery)" to evaluate to the unknown truth value. The join condition "Y.WORKDEPT = DEPARTMENT.DEPTNO", however, is always true:
 |
The following figure is the "AND" truth table for search conditions; "TRUE AND UNKNOWN" causes the search condition in the query to be "UNKNOWN," as indicated above.
 |
No rows satisfy the search condition, so no employee is listed for department A00; exactly the result wanted in this case.
You can use a subquery to test for the existence of a row satisfying some condition. In this case, the subquery is linked to the outer-level query by the predicate EXISTS or NOT EXISTS. (Refer to the DB2 Server for VSE & VM SQL Reference manual for the syntax of the EXISTS predicate.)
When you link a subquery to an outer query by an EXISTS predicate, the subquery does not return a value. Rather, the EXISTS predicate is true if the answer set of the subquery contains one or more rows, and false if it contains no rows.
The EXISTS predicate is often used with correlated subqueries. The example below lists the departments that currently have no entries in the PROJECT table:
DECLARE C1 CURSOR FOR
SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO
|
You may connect the EXISTS and NOT EXISTS predicates to other predicates by using AND and OR in the WHERE clause of the outer-level query.
Unqualified correlated references are allowed. For example, assume that table EMP has a column named SALARY and that table DEPT has a column named BUDGET, but no column named SALARY.
SELECT * FROM EMP
WHERE EXISTS (SELECT * FROM DEPT
WHERE BUDGET < SALARY)
In this example, the system checks the innermost FROM clause for a SALARY column. Not finding one, it then checks the next innermost FROM clause (which in this case is the outer FROM clause). It is only necessary to use a qualified correlated reference when you want the system to ignore a column with the same name in the innermost tables.
To assist you in these situations, a warning message SQLCODE +12 (SQLSTATE '01545') is issued whenever an SQL statement is executed that contains an unqualified correlated reference in a subquery.
The UNION operator enables you to combine two or more outer-level queries into a single query. Each of the queries connected by UNION is executed to produce an answer set; these answer sets are then combined, and duplicate rows are eliminated from the result.
When ALL is used with UNION (that is, UNION ALL), duplicate rows are not eliminated when two or more outer-level queries are combined into a single query.
If you are using the ORDER BY clause, you must write it after the last query in the UNION. The system applies the ordering to the combined answer set before it delivers the results to your program using the usual cursor mechanism.
It is possible (though unusual) to write a query using the UNION operator that does not return results with a cursor. In this instance, only one row must be retrieved from the tables, and an INTO clause must be placed only in the first query.
The UNION operator is useful when you want to merge lists of values derived from two or more tables and eliminate any duplicates from the final result. UNION ALL will give better performance, however, because no internal sort is done. This sort is done with the UNION operator to facilitate the elimination of duplicates.
When both UNION and UNION ALL are used in the same query, processing is from left-to-right. If the last union operation is UNION, the duplicates will be eliminated from the final results; if it is UNION ALL, the duplicates will not be eliminated. However, the left-to-right priority can be altered by the use of parenthesis. A parenthesized subselect is evaluated first, followed, from left-to-right, by the other components of the statement. For example, the results of the following two queries, where A, B, and C are subselects, could be quite different:
A UNION (B UNION ALL C) (A UNION B) UNION ALL C
In the following example, the query returns all projects for which the estimated mean number of employees is greater than 0.50, and it returns all the projects where the proportion of employee time spent on the project is greater than 0.50:
SELECT PROJNO,'MEAN' FROM PROJ_ACT WHERE ACSTAFF > .50 UNION SELECT PROJNO,'PROPORTION' FROM EMP_ACT WHERE EMPTIME > .50 |
The database manager combines the results of both queries, eliminates the duplicates, and returns the final result in ascending order.
| Note: | The ascending order is a direct result of the internal sort, which is performed to facilitate the elimination of duplicates. |
PROJNO EXPRESSION ------ ------------ AD3110 MEAN AD3110 PROPORTION AD3111 MEAN AD3111 PROPORTION AD3112 MEAN AD3112 PROPORTION AD3113 MEAN AD3113 PROPORTION IF1000 MEAN IF1000 PROPORTION IF2000 MEAN IF2000 PROPORTION MA2100 MEAN MA2100 PROPORTION MA2110 MEAN MA2110 PROPORTION MA2111 MEAN MA2111 PROPORTION MA2112 MEAN MA2112 PROPORTION MA2113 MEAN MA2113 PROPORTION OP1010 MEAN OP1010 PROPORTION OP2000 MEAN OP2010 MEAN OP2010 PROPORTION OP2011 MEAN OP2011 PROPORTION OP2012 MEAN OP2012 PROPORTION PL2100 MEAN PL2100 PROPORTION |
To connect queries by the UNION operator, you must ensure that they obey the following rules:
Unions between columns that have the same data type and the same length produce a column with that type and length. If they are not of the same type and length but they are union-compatible, the resulting column-type is a combination of the two original columns.
The results of a UNION between two union-compatible items is summarized below. The first row and first column of the table represent the data-type of the first and second columns of the UNION join.
|
| CHAR | VARCHAR | GRAPHIC | VARGRAPHIC |
| CHAR | CHAR | VARCHAR | ERROR | ERROR |
| VARCHAR | VARCHAR | VARCHAR | ERROR | ERROR |
| GRAPHIC | ERROR | ERROR | GRAPHIC | VARGRAPHIC |
| VARGRAPHIC | ERROR | ERROR | VARGRAPHIC | VARGRAPHIC |
The length attribute of the resulting column will be the greater of the length attributes of the original columns.
The UNION operators between columns that have the same character subtype and CCSID produce a column with that subtype and CCSID. If they do not have the same subtype and CCSID, the resulting subtype and CCSID are determined following specific rules. For a detailed discussion of these rules, refer to the DB2 Server for VSE & VM SQL Reference manual.
|
| SMALLINT | INTEGER | DECIMAL | SINGLE PRECISION | DOUBLE PRECISION |
| SMALLINT | SMALLINT | INTEGER | DECIMAL | DOUBLE PRECISION | DOUBLE PRECISION |
| INTEGER | INTEGER | INTEGER | DECIMAL | DOUBLE PRECISION | DOUBLE PRECISION |
| DECIMAL | DECIMAL | DECIMAL | DECIMAL | DOUBLE PRECISION | DOUBLE PRECISION |
| SINGLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION | SINGLE PRECISION | DOUBLE PRECISION |
| DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION |
When both of the original columns are DECIMAL data-types, special rules apply for determining the scale and precision of the resulting column.
Where s is the scale of the first column of the UNION join, s' is the scale of the second column, p is the precision of the first column, and p' is the precision of the second, the resulting column's precision is:
MIN( 31,MAX( s , s' ) + MAX( p-s , p'-s' ) )
The scale of the resulting column is the maximum scale of the original columns of the UNION join, MAX( s, s').
When a UNION is performed on a DECIMAL and either an INTEGER or SMALLINT column, the resulting column's scale and precision can be calculated with the previous formulas. However, remember to substitute 11 and 0 for the precision and scale of an INTEGER column, and 5 and 0 for a SMALLINT column.
|
| DATE | TIME | TIMESTAMP |
| DATE | DATE | ERROR | ERROR |
| TIME | ERROR | TIME | ERROR |
| TIMESTAMP | ERROR | ERROR | TIMESTAMP |
| Note: | CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are not union-compatible with DATE, TIME, or TIMESTAMP. |