Scalar and nested subqueries in DB2

Subqueries

The SELECT that contains the subquery is the outer query. The inner fullselect is nested. IBM’s language:

• A subquery is a fullselect enclosed in parentheses, typically in a search condition
• A scalar fullselect (scalar subquery) is a fullselect used as an expression that returns a single column value from a single row
• A fullselect in FROM is a nested table expression

Subqueries may themselves contain subqueries, so statements form a hierarchy. Each level has table designators (FROM, or the target of UPDATE/MERGE at the top). A search condition may reference columns from its own FROM and from higher levels; a higher-level reference is a correlated reference.

If the inner query does not depend on the current outer row, it is uncorrelated: Db2 can run it once and reuse the result. The examples on this page stay uncorrelated unless noted.

Scalar subquery

A scalar-fullselect is a parenthesized fullselect that returns one row and one column. Rules from the SQL Reference:

• One row, one value — that value is the result of the expression
• Zero rows — the expression is null
• More than one row — an error (SQLCODE -811, SQLSTATE 21000)
• More than one column is also invalid for a scalar context (SQLCODE -412 / SQLSTATE 42823 in the “multiple columns where one is allowed” family)

1
2
3
4
5
6
-- Scalar subquery in WHERE: compare to a single number
SELECT PRODUCT, PRICE
  FROM PRODUCTS AS A
 WHERE PRICE BETWEEN
       2 * (SELECT MIN(PRICE) FROM PRODUCTS)
   AND 0.5 * (SELECT MAX(PRICE) FROM PRODUCTS);

MIN and MAX over the whole table each return one row, so these scalar subqueries are safe. Writing (SELECT PRICE FROM PRODUCTS) without aggregation or a unique filter is not safe if PRODUCTS has many rows.

Scalar subquery in the SELECT list

Each outer row can carry extra columns computed by scalar subqueries. If the inner query uses the outer row’s key, it is correlated (see the next tutorial). Even then the inner query must still return at most one row per outer row:

1
2
3
4
SELECT PART,
       (SELECT PRICE FROM PARTPRICE WHERE PART = A.PART) AS PRICE,
       (SELECT ONHAND# FROM INVENTORY WHERE PART = A.PART) AS ON_HAND
  FROM PARTS AS A;

If PARTPRICE can have two prices for one PART, this statement fails at run time with -811 when that part is processed. Enforce uniqueness (primary key on PART) or aggregate (MAX(PRICE)) if the business rule allows it.

If no PARTPRICE row exists, PRICE is null for that PART—left-join style behavior without writing a join. Many shops prefer an explicit LEFT OUTER JOIN for the same pattern because the join is easier to index-check and does not abort on duplicates; it duplicates the outer row instead. Choose based on whether two matches are an error or extra result rows.

Where scalar fullselects are not allowed

IBM lists contexts that reject a scalar fullselect, including:

• CHECK constraints on CREATE/ALTER TABLE
• CREATE VIEW … WITH CHECK OPTION
• Some SQL function RETURN expressions (already restricted)
• An argument of a CALL for an input parameter
• An argument to an aggregate function (except the XML-expression of XMLAGG)
• ORDER BY and GROUP BY clauses
• A join-condition in ON for inner and outer joins

If you need that computed value as a grouping key, compute it in a nested table expression first, then GROUP BY the resulting column.

Subquery in WHERE

A predicate in WHERE can contain a subquery. Like any predicate it may be parenthesized, prefixed with NOT, and combined with AND/OR. The subquery’s result type must match what the predicate expects.

IN with a subquery

Classic uncorrelated pattern: keep outer rows whose key appears in an inner list.

1
2
3
4
5
6
7
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8C10.EMP
 WHERE EMPNO IN (
       SELECT EMPNO
         FROM DSN8C10.EMPPROJACT
        WHERE PROJNO = 'MA2111'
       );

The inner SELECT returns one column (EMPNO) and any number of rows. Duplicates in the inner list do not duplicate outer employees. expression IN (fullselect) is equivalent to expression = ANY (fullselect). NOT IN (fullselect) is equivalent to expression <> ALL (fullselect).

NOT IN and nulls: if the subquery returns a null, NOT IN can become UNKNOWN for every outer row, so you get no results. Prefer NOT EXISTS for “anti-join” logic when the inner column is nullable. That difference is a main reason shops teach EXISTS on the correlated-subqueries page.

Basic comparison to a scalar subquery

Equality and other operators require a single inner value:

1
2
3
4
5
6
7
8
9
10
11
SELECT LASTNAME, FIRSTNME, SALARY
  FROM DSN8C10.EMP AS X
 WHERE EMPNO = (
       SELECT RESPEMP
         FROM PROJA1
        WHERE MAJPROJ = 'SECRET'
       UNION
       SELECT RESPEMP
         FROM PROJA2
        WHERE MAJPROJ = 'SECRET'
       );

Here the scalar fullselect is itself a UNION. UNION removes duplicate RESPEMP values so that if the same employee appears in both project tables you still get one value. If twodifferent employees are responsible, the UNION still returns two rows and the outer equality fails with -811. That is the correct failure if “the” responsible employee must be unique.

1
2
3
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY >= (SELECT AVG(SALARY) FROM DSN8C10.EMP);

Quantified predicates

ANY and SOME are synonyms. ALL is the universal test:

• expr > ALL (fullselect) — true if the inner result is empty or the comparison is true for every inner value
• expr > ANY (fullselect) — true if it is true for at least one inner value; false if the inner result is empty

Empty inner results make ALL-comparisons true and ANY-comparisons false. Null inner values make the predicate UNKNOWN unless a definite true/false already decided it. These rules are why people rewrite ALL/ANY into MAX/MIN + scalar subquery when the inner column is NOT NULL.

EXISTS in WHERE

EXISTS (fullselect) is true when the inner query returns at least one row, false when it returns none, and is never UNKNOWN. Column values are ignored; SELECT * is the usual inner select list. There is no “EXISTS NOT” keyword; write NOT EXISTS (fullselect).

Uncorrelated EXISTS is rare but legal (a subquery that does not mention the outer tables). It is either true for every outer row or false for every outer row. Useful EXISTS almost always correlates. See the correlated-subqueries page for the standard pattern.

Subquery in HAVING

HAVING applies a search condition to each group. A subquery there often compares a group aggregate to a set-level aggregate:

1
2
3
4
SELECT WORKDEPT, AVG(SALARY) AS DEPT_AVG
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM DSN8C10.EMP);

The inner AVG(SALARY) does not reference WORKDEPT, so it is uncorrelated and can run once. You could also compare COUNT(*) to a scalar subquery that counts a threshold stored in a parameter table.

A subquery in HAVING may be correlated to the group: the inner query can reference a grouping column of the outer grouped result. Conceptually it runs per group. Column names in HAVING still must be grouping columns, aggregates, or correlated references—the subquery does not waive those rules for the rest of the HAVING clause.

1
2
3
4
5
6
7
8
9
10
11
SELECT WORKDEPT, COUNT(*) AS N
  FROM DSN8C10.EMP AS E
 GROUP BY WORKDEPT
HAVING COUNT(*) > (
       SELECT COUNT(*)
         FROM DSN8C10.EMPPROJACT AS P
        WHERE P.EMPNO IN (
              SELECT EMPNO FROM DSN8C10.EMP E2
               WHERE E2.WORKDEPT = E.WORKDEPT
              )
       );

That nested form is legal but heavy. Beginners should prefer joins or a single well-keyed IN subquery before stacking three levels. Depth is allowed; readability and access paths still matter.

Uncorrelated execution

For the IN example with PROJNO = 'MA2111', the inner query does not mention EMP columns from the outer FROM. Db2 can materialize the employee-number list once, then probe EMP. That is the performance story you want when the inner filter is a constant. If you accidentally write WHERE inner.WORKDEPT = outer.WORKDEPT, you have switched to a correlated subquery and the inner query is logically per outer row (the optimizer may rewrite it, but you should still know the difference).

SQLCODE reminders

• -811 — scalar fullselect / SELECT INTO returned more than one row
• -412 — subquery returns more than one column where one is required
• -104 / syntax — missing parentheses around the nested fullselect

FETCH FIRST 1 ROW ONLY inside a scalar subquery limits rows, but without ORDER BY the surviving row is not a business “top” row. If you need the latest hire, ORDER BY HIREDATE DESC FETCH FIRST 1 ROW ONLY inside the scalar fullselect (supported in scalar fullselects) makes the choice deterministic.

Explain It Like I'm Five

A subquery is a question inside a question. The outer question is “which employees?” The inner question is “which employee numbers are on project MA2111?” You finish the inner list first, then use that list like a checklist. A scalar subquery is a question that must come back with one number (or “I don’t know,” which is null). If it comes back with two numbers, Db2 stops and says the question was unfair.

Exercises

1. List employees whose EMPNO appears in EMPPROJACT for any project starting with 'MA' using IN (subquery).
2. Write a scalar subquery in the SELECT list that shows each department’s employee count next to DEPT.DEPTNO. State what happens if you forget GROUP BY or a correlation.
3. Using HAVING, keep only jobs whose AVG(SALARY) is below the overall AVG(SALARY).
4. Explain why WHERE SALARY > (SELECT SALARY FROM EMP WHERE WORKDEPT = 'A00') can fail with -811.
5. Rewrite expression IN (fullselect) as the equivalent ANY quantified predicate.