DB2 Write subqueries

Subquery vocabulary and query blocks

A subquery is a parenthesized fullselect nested in a larger statement. The surrounding SELECT or data-change statement is often called the outer statement, while each SELECT level is a query block. A subquery can appear in a select list, WHERE clause, HAVING clause, expression, or FROM clause when that location accepts the corresponding result shape. Parentheses establish scope, but table aliases and qualified column names make that scope understandable to people.

• A scalar subquery returns one column and at most one row.
• A row subquery returns one row containing multiple columns.
• A table subquery returns zero or more rows and columns as a table expression.
• A noncorrelated subquery is self-contained; it uses no outer column.
• A correlated subquery references a column from an outer query block.

Result shape matters more than the number of SELECT keywords. A comparison to one value needs a scalar result. IN accepts a set of comparable values. EXISTS only asks whether a row exists. A FROM-clause nested table expression supplies a table. Choosing the form that matches the question prevents many syntax and cardinality errors.

Scalar subqueries: one value

A scalar subquery can be used wherever DB2 accepts a compatible expression. It must return exactly one column and no more than one row. If it returns no row, its value is NULL. If it returns two or more rows, DB2 cannot select one value and normally reports SQLCODE -811. Aggregate functions are convenient because an aggregate without GROUP BY returns one row even when no input row qualifies.

1
2
3
4
5
6
7
8
9
SELECT E.EMPNO,
       E.LASTNAME,
       E.SALARY,
       (SELECT AVG(SALARY)
          FROM EMPLOYEE) AS COMPANY_AVG
  FROM EMPLOYEE E
 WHERE E.SALARY > (SELECT AVG(SALARY)
                     FROM EMPLOYEE)
 ORDER BY E.SALARY DESC;

Both scalar subqueries are noncorrelated because neither refers to E. The optimizer is free to transform common expressions; SQL text does not promise that the average is physically calculated in the way a procedural reading suggests. The comparison is valid because AVG returns one scalar value. The selected scalar value also gives each qualifying employee useful context.

Avoid accidental multiple rows

A lookup by a nonunique business name is not guaranteed to be scalar. Prefer a primary key or unique key predicate when the business rule requires exactly one row. Adding MAX, MIN, or FETCH FIRST 1 ROW ONLY merely to suppress -811 can hide duplicate data and produce an arbitrary answer. Use such logic only when “highest,” “lowest,” or a documented first row is the actual requirement, with deterministic ordering where applicable.

Row subqueries: compare several values together

A row subquery returns multiple columns from at most one row. A row-value expression on the other side supplies the same degree: the count and compatible types of its components must match. This form is useful when a combination of values has meaning and should be tested as a unit rather than through separate, potentially inconsistent lookups.

1
2
3
4
5
6
SELECT E.EMPNO, E.LASTNAME, E.WORKDEPT, E.JOB
  FROM EMPLOYEE E
 WHERE (E.WORKDEPT, E.JOB) =
       (SELECT P.WORKDEPT, P.JOB
          FROM EMPLOYEE P
         WHERE P.EMPNO = '000010');

The inner predicate uses the employee number and should return at most one row when that column is unique. The outer row-value expression has two elements, matching WORKDEPT and JOB from the subquery. If the subquery returns no row, the scalar row result contains null values and the equality is not true. If nullable columns participate, analyze three-valued logic rather than assuming two missing values compare as equal.

Table subqueries and nested table expressions

A table subquery produces a relation: zero or more rows with one or more columns. One common form is a nested table expression in the FROM clause. It must have a correlation name so the outer query can qualify its columns. This form is especially useful when a calculation such as aggregation must occur before a join or filter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT D.DEPTNO,
       D.DEPTNAME,
       S.EMPLOYEE_COUNT,
       S.TOTAL_SALARY
  FROM DEPARTMENT D
  INNER JOIN
       (SELECT WORKDEPT,
               COUNT(*) AS EMPLOYEE_COUNT,
               SUM(SALARY) AS TOTAL_SALARY
          FROM EMPLOYEE
         GROUP BY WORKDEPT) AS S
    ON S.WORKDEPT = D.DEPTNO
 WHERE S.EMPLOYEE_COUNT >= 5
 ORDER BY S.TOTAL_SALARY DESC;

The nested expression S groups employees before joining departments. It is not a permanent view and exists only for this statement. DB2 might merge it into the broader access path or materialize an intermediate result. A common table expression could make the same logical stage more readable, especially if referenced more than once. Use EXPLAIN to discover the physical choice rather than inferring it from formatting.

Noncorrelated and correlated subqueries

A noncorrelated subquery can stand alone. Its text contains every table reference needed to calculate its answer. The company-average example is noncorrelated. A correlated subquery reaches outward through an alias, so its logical answer changes with the current outer row. Qualification is essential: without aliases, a mistyped inner column can bind to an outer column and silently change the meaning.

1
2
3
4
5
6
7
SELECT E.EMPNO, E.LASTNAME, E.WORKDEPT, E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY >
       (SELECT AVG(D.SALARY)
          FROM EMPLOYEE D
         WHERE D.WORKDEPT = E.WORKDEPT)
 ORDER BY E.WORKDEPT, E.SALARY DESC;

E.WORKDEPT is the correlation reference. Conceptually, DB2 finds the average for the current employee's department and compares the employee's salary with it. That explanation is useful for correctness, but it is not an execution guarantee. DB2 can decorrelate a query, transform it into a join-like operation, cache values, or choose another strategy. Good statistics and an index beginning with useful inner correlation and filtering columns can make a large difference.

EXISTS and NOT EXISTS

EXISTS is true when its subquery returns at least one row. It does not consume the selected value, so SELECT 1 communicates intent clearly. The inner query is commonly correlated to describe the related row that must exist. DB2 can stop searching after proving existence, subject to its chosen access path.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME
  FROM CUSTOMER C
 WHERE EXISTS (
       SELECT 1
         FROM ORDERS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
          AND O.STATUS = 'OPEN'
      );
 
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME
  FROM CUSTOMER C
 WHERE NOT EXISTS (
       SELECT 1
         FROM ORDERS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
          AND O.STATUS = 'OPEN'
      );

The first statement finds customers with at least one open order. The second finds customers for whom no matching open order exists. An index such as ORDERS (CUSTOMER_ID, STATUS), selected according to the wider workload, can support the correlation and status test. Do not use SELECT * inside EXISTS for readability; although DB2 cares about row existence, SELECT 1 states that no inner column value is being requested.

IN, NOT IN, and the NULL trap

IN tests membership in a set and is natural when an outer value must equal one of the subquery values. Positive IN and a logically equivalent EXISTS are often candidates for similar optimizer transformations. Choose clear SQL, then verify the access path.

1
2
3
4
5
6
7
SELECT E.EMPNO, E.LASTNAME, E.WORKDEPT
  FROM EMPLOYEE E
 WHERE E.WORKDEPT IN (
       SELECT D.DEPTNO
         FROM DEPARTMENT D
        WHERE D.DIVISION = 'EAST'
      );

NOT IN requires more care. SQL comparisons can be true, false, or unknown. Suppose the inner result is ('A00', 'B01', NULL). For an outer value 'C01', DB2 can prove it differs from 'A00' and 'B01', but it cannot prove it differs from the unknown value. The overall NOT IN predicate is unknown, and WHERE retains only true rows. The apparently safe 'C01' row is therefore removed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Risky when ORDERS.CUSTOMER_ID can be NULL
SELECT C.CUSTOMER_ID
  FROM CUSTOMER C
 WHERE C.CUSTOMER_ID NOT IN (
       SELECT O.CUSTOMER_ID
         FROM ORDERS O
        WHERE O.STATUS = 'CANCELLED'
      );
 
-- NULL-safe anti-match pattern
SELECT C.CUSTOMER_ID
  FROM CUSTOMER C
 WHERE NOT EXISTS (
       SELECT 1
         FROM ORDERS O
        WHERE O.STATUS = 'CANCELLED'
          AND O.CUSTOMER_ID = C.CUSTOMER_ID
      );

Prefer NOT EXISTS for an anti-match unless the subquery column is constrained NOT NULL or the SQL explicitly excludes NULL and that exclusion matches the business rule. Also consider an outer NULL: it cannot equal ordinary inner values, but NOT IN still does not turn unknown comparisons into true. Constraints are valuable because they document the data rule and can give the optimizer better information.

ANY, SOME, and ALL quantified predicates

ANY and ALL combine a comparison operator with a set. SOME is a synonym for ANY. X > ANY (subquery) means X is greater than at least one returned value. X > ALL (subquery) means X is greater than every returned value. The operator is important: = ANY is equivalent in meaning to IN, while <> ALL resembles NOT IN and has the same kind of NULL risk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Employees paid more than at least one employee in department D11
SELECT E.EMPNO, E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY > ANY (
       SELECT D.SALARY
         FROM EMPLOYEE D
        WHERE D.WORKDEPT = 'D11'
          AND D.SALARY IS NOT NULL
      );
 
-- Employees paid more than every employee in department D11
SELECT E.EMPNO, E.SALARY
  FROM EMPLOYEE E
 WHERE E.SALARY > ALL (
       SELECT D.SALARY
         FROM EMPLOYEE D
        WHERE D.WORKDEPT = 'D11'
          AND D.SALARY IS NOT NULL
      );

Empty sets deserve explicit thought. A comparison against ANY of an empty set is false because no value proves the condition. A comparison against ALL of an empty set is true because no returned value disproves it. NULL values can make a quantified comparison unknown when no decisive true or false result exists. Filtering NULL is appropriate only when ignoring missing values matches the requirement.

Subqueries inside common predicates

A subquery is not a predicate by itself in most everyday patterns; its result is used by a predicate or expression. Match the predicate to the expected result and business meaning.

• Use =, <, >, <=, >=, or <> with a scalar subquery when exactly one comparison value is intended.
• Use IN for membership and EXISTS for the presence of a qualifying related row.
• Use NOT EXISTS for an anti-match, especially when the inner key might contain NULL.
• Use ANY or ALL when the requirement genuinely compares against at least one or every member of a set.
• Use a scalar subquery as an endpoint in expressions such as BETWEEN only when its one-value cardinality and NULL behavior are understood.

WHERE and HAVING accept only true rows. False and unknown are both rejected. That one rule explains most surprising NULL behavior in subquery predicates. Test with empty inner sets, an inner NULL, an outer NULL, one matching row, no matching row, and duplicate values.

Performance, indexes, and EXPLAIN

Do not assume that joins are always faster than subqueries or that noncorrelated forms execute only once. DB2's optimizer transforms SQL based on semantics, statistics, available indexes, data distribution, estimated cardinality, sort cost, and subsystem capabilities. Two different-looking statements can receive similar access paths, while a small semantic difference can prevent a transformation.

1. Keep catalog statistics current enough to represent table size, key cardinality, frequency, and correlation that matter to the statement.
2. Qualify columns and correlate on compatible data types to avoid ambiguity and conversion work.
3. Consider indexes beginning with inner correlation and selective predicate columns, while accounting for insert, update, storage, and other-query costs.
4. Run EXPLAIN and inspect access methods, matching columns, index-only possibilities, join or subquery transformations, sorts, materialization, and estimated rows.
5. Measure elapsed time, CPU, getpages, synchronous reads, work-file use, and executions with representative host-variable values and production-like cardinalities.
6. Compare a clear equivalent rewrite—such as EXISTS versus a join—while checking duplicates and NULL semantics before declaring the statements equivalent.

A join can multiply outer rows when several inner matches exist, whereas EXISTS returns each qualifying outer row according to the outer query. Adding DISTINCT to repair that multiplication can add a sort and conceal a semantic mistake. Similarly, replacing a scalar subquery with a join can alter behavior when the inner relation is not unique. Correct cardinality comes before tuning.

Common errors and debugging

• SQLCODE -811: a scalar or row subquery returned more than one row. Repair the data rule or predicate; do not arbitrarily discard rows.
• Unexpected no rows: inspect NOT IN for an inner NULL and remember that WHERE removes unknown results.
• Wrong correlation: qualify every shared column name. An unqualified name might resolve in a different query block than intended.
• Degree mismatch: a row comparison must have the same number of expressions on both sides, with comparable data types.
• Duplicate outer rows after a rewrite: a join preserves all matching combinations, while EXISTS only tests presence.
• Poor performance: check stale statistics, weak inner access, nonmatching indexes, implicit casts, low selectivity, materialization, and repeated correlated work in EXPLAIN and runtime data.

Debug from the inside out. Run a noncorrelated inner query independently. For a correlated query, replace the outer reference with one known test value and inspect the rows. Then test boundary cases deliberately. Keep the final SQL declarative and clear; temporary diagnostic literals should not replace proper correlation in production.

Explain it like I'm 5

Imagine a teacher has a big list of children. A subquery is a smaller question written on a note. A scalar note asks for one answer, such as the class average. A row note asks for a pair, such as one child's team and job. A table note returns a whole smaller list. A noncorrelated note can be answered by itself. A correlated note says, “For the child I am looking at right now, does that child have an unfinished assignment?”

EXISTS means “Did I find at least one?” and NOT EXISTS means “I found none.” IN means “Is this answer on my list?” NULL is a smudged, unreadable answer. With NOT IN, one smudged answer can stop us from confidently saying something is not on the list. That is why NOT EXISTS is often the safer way to ask whether a matching item is missing.

Exercises

1. Write a scalar subquery that returns employees whose salary is above the company average. Add the average to the select list and explain its zero-row behavior.
2. Write a correlated scalar subquery that compares each employee's salary with the average for that employee's department. Identify every outer reference.
3. Find departments that have at least one employee with JOB = 'MANAGER' using EXISTS, then find departments with none using NOT EXISTS.
4. Create sample inner results containing two department numbers and NULL. Predict which rows survive NOT IN, then rewrite the anti-match with NOT EXISTS.
5. Write one query using > ANY and another using > ALL. Predict each result for an empty inner set and for a set containing a NULL.
6. Build a row subquery comparing an employee's department and job with those of a uniquely identified reference employee. Test the missing-reference case.
7. Place a grouped salary subquery in the FROM clause, give it a correlation name, and join it to DEPARTMENT. Explain whether SQL syntax guarantees materialization.
8. EXPLAIN equivalent IN and EXISTS statements. Compare access paths, estimated rows, matching columns, and runtime metrics with representative data.