DB2 Anti-Pattern: Correlated Subqueries

What correlation means

A query contains separate query blocks. An inner block is correlated when it references a column exposed by an outer block. In the following statement, the inner alias I refers to INVOICE, while C belongs to the outer CUSTOMER block. The predicate I.CUSTOMER_ID = C.CUSTOMER_ID connects the two blocks and gives the subquery a different search value for each customer.

1
2
3
4
5
6
7
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE (SELECT COUNT(*)
          FROM INVOICE AS I
         WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
           AND I.PAYMENT_STATUS = 'UNPAID') > 0;

The logical model says to consider each customer and evaluate the inner condition using that customer's identifier. Do not turn that model into a literal promise that Db2 always executes the written subquery once per row. The optimizer can decorrelate or transform eligible SQL, choose a join method, and reorder work while preserving the result. Conversely, readable SQL can still produce repeated nested-loop probes. The selected access path—not indentation—is the evidence.

Why correlated SQL can become expensive

The dangerous combination is a large outer result and expensive inner work. If 500,000 customers reach the correlated block and each probe scans many invoice pages, total work can grow dramatically. Even an individually quick probe matters when multiplied by a high execution count. A correlated aggregate can be worse because Db2 may need to find and process every matching inner row before producing COUNT, SUM, AVG, MIN, or MAX.

• Repeated access: the same inner table or index pages can be visited for many outer values.
• Missing matching keys: without an index beginning with useful correlation columns, each probe can scan a broad range.
• Work inside the subquery: DISTINCT, grouping, expressions, sorts, and non-indexable predicates increase the cost of every execution.
• Bad cardinality estimates: stale or incomplete statistics can make repeated probes look cheaper than they are.
• High statement frequency: modest per-execution waste becomes important in an online transaction that runs millions of times.

Cost is not determined by table size alone. A very large INVOICE table can support fast probes when CUSTOMER_ID and the relevant filter columns form selective index access. A small inner table might be cheaper to scan once and join. Outer filtering also matters: ten key-selected customers create a different problem from an unfiltered customer scan.

Use EXISTS when the question is “does any row exist?”

COUNT answers how many rows qualify. If the application needs only yes or no, EXISTS is the direct operator. The select list inside EXISTS does not define output data; the truth value depends only on whether a qualifying row exists. Duplicate inner matches do not change the answer. This gives Db2 a clear semijoin-style requirement and avoids asking SQL to compute a complete count solely to compare it with zero.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Avoid counting every matching invoice for a yes/no question
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE (SELECT COUNT(*)
          FROM INVOICE AS I
         WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
           AND I.PAYMENT_STATUS = 'UNPAID') > 0;
 
-- Express the requirement directly
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE EXISTS
       (SELECT 1
          FROM INVOICE AS I
         WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
           AND I.PAYMENT_STATUS = 'UNPAID');

EXISTS does not force one particular access path, and writing SELECT 1 is a convention, not a special optimization command. Db2 can stop proving existence after a match because later matches cannot change true to something “more true.” The optimizer might also transform the statement. Use NOT EXISTS for “no qualifying related row.” Unlike NOT IN, NOT EXISTS can express an anti-match without the surprising UNKNOWN result caused by a NULL in the NOT IN subquery result, provided the correlation predicates themselves express the intended NULL rules.

Rewrite repeated aggregates with a CTE and join

A scalar correlated aggregate is common in reports: for every customer, calculate total unpaid balance. A set-based alternative groups INVOICE once by CUSTOMER_ID and joins the grouped result to CUSTOMER. This can expose a broader range of join strategies and avoid logically repeating the aggregate. A CTE improves structure here, but it is not a command to materialize results; Db2 is free to merge, transform, or otherwise optimize it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Correlated scalar aggregate
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       (SELECT SUM(I.AMOUNT_DUE)
          FROM INVOICE AS I
         WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
           AND I.PAYMENT_STATUS = 'UNPAID') AS UNPAID_TOTAL
  FROM CUSTOMER AS C;
 
-- Set-based rewrite
WITH UNPAID_BY_CUSTOMER AS
(
  SELECT I.CUSTOMER_ID,
         SUM(I.AMOUNT_DUE) AS UNPAID_TOTAL
    FROM INVOICE AS I
   WHERE I.PAYMENT_STATUS = 'UNPAID'
   GROUP BY I.CUSTOMER_ID
)
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       U.UNPAID_TOTAL
  FROM CUSTOMER AS C
  LEFT JOIN UNPAID_BY_CUSTOMER AS U
    ON U.CUSTOMER_ID = C.CUSTOMER_ID;

The LEFT JOIN is important. For a customer with no unpaid invoice, the scalar SUM returns NULL and the outer customer row remains. An INNER JOIN would remove that customer and change the answer. Adding COALESCE(U.UNPAID_TOTAL, 0) is valid only if the business rule says “no invoices means zero”; it changes NULL to zero and should be intentional. Aggregating before joining also guarantees one grouped row per customer key, preventing invoice duplicates from multiplying outer rows.

Use window functions for latest-row and group comparisons

Developers often correlate MAX to find the latest event for each order. The pattern can return more than one row when multiple events share the same timestamp. A window function can rank each group once and apply an explicit tie-breaker. ROW_NUMBER chooses one row; RANK or DENSE_RANK intentionally keeps ties. Those are different requirements, so choose before tuning.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Correlated latest timestamp; ties can return multiple rows
SELECT E.ORDER_ID,
       E.EVENT_TYPE,
       E.EVENT_TS
  FROM ORDER_EVENT AS E
 WHERE E.EVENT_TS =
       (SELECT MAX(E2.EVENT_TS)
          FROM ORDER_EVENT AS E2
         WHERE E2.ORDER_ID = E.ORDER_ID);
 
-- Deterministically choose one latest row per order
WITH RANKED_EVENT AS
(
  SELECT E.ORDER_ID,
         E.EVENT_TYPE,
         E.EVENT_TS,
         E.EVENT_ID,
         ROW_NUMBER() OVER
           (PARTITION BY E.ORDER_ID
                ORDER BY E.EVENT_TS DESC, E.EVENT_ID DESC) AS RN
    FROM ORDER_EVENT AS E
)
SELECT ORDER_ID,
       EVENT_TYPE,
       EVENT_TS
  FROM RANKED_EVENT
 WHERE RN = 1;

EVENT_ID makes the ordering deterministic when timestamps tie. If the requirement is to return every event at the maximum timestamp, the original semantics or a DENSE_RANK rewrite may be appropriate instead. Window processing can require sorting unless an access path supplies useful order, so it is not automatically cheaper. It often becomes attractive when the original query evaluates a group aggregate for many rows.

Window functions can also replace repeated group comparisons. For example, an employee compared with the average salary of that employee's department can use AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID). The window result remains attached to each employee row, avoiding a separate aggregate join. Put the window expression in a nested table expression or CTE, then filter the derived average in the outer query.

Choose the pattern that matches the requirement

When a correlated subquery is appropriate

Correlated syntax is not inherently wrong. EXISTS and NOT EXISTS often provide the clearest statement of a semijoin or antijoin rule. A transaction that retrieves one account and checks for one matching exception may perform only a few selective probes. Rewriting that statement into a large pre-aggregation could process far more data. Correlation can also be the cleanest way to express conditions whose meaning genuinely depends on each outer value.

• The outer query is strongly selective and produces few rows.
• The inner probe uses matching index columns and normally finds or rejects quickly.
• EXISTS or NOT EXISTS accurately expresses the business question.
• Db2 EXPLAIN shows an efficient transformation or access path.
• Representative monitoring confirms acceptable total cost at expected frequency.

Prefer clarity first, then verify. A longer join is not automatically faster, and a CTE is not automatically evaluated once. Db2's cost-based optimizer considers available indexes, statistics, predicates, join methods, sort requirements, parallelism, and estimated cardinalities. Tune the chosen plan and workload, not a visual SQL style.

Index the probe, not just the table

For I.CUSTOMER_ID = C.CUSTOMER_ID AND I.PAYMENT_STATUS = 'UNPAID', an index beginning with CUSTOMER_ID can let Db2 probe one customer's keys. An index on (CUSTOMER_ID, PAYMENT_STATUS) can narrow the matching range further. Reversing the keys to (PAYMENT_STATUS, CUSTOMER_ID) might support other workloads and can still match both equalities, but key order affects clustering, filtering patterns, skip behavior, and queries that provide only one predicate. There is no universal order detached from the workload.

1
2
3
4
5
6
7
-- Illustrative index for the EXISTS example
CREATE INDEX APP.IX_INVOICE_CUSTOMER_STATUS
    ON APP.INVOICE (CUSTOMER_ID, PAYMENT_STATUS);
 
-- Illustrative index for latest-event probes or ordering
CREATE INDEX APP.IX_EVENT_ORDER_TIME
    ON APP.ORDER_EVENT (ORDER_ID, EVENT_TS DESC, EVENT_ID DESC);

Extra indexes consume disk and buffer-pool space and add work to INSERT, DELETE, UPDATE, logging, COPY, RECOVER, REORG, and RUNSTATS. Include columns needed only for index-only access when measured savings justify that operational cost. Keep catalog statistics current, including suitable column-group statistics when correlated predicates have related distributions. An index definition does not guarantee Db2 will choose it.

NULL, duplicate, and cardinality traps

A scalar subquery must return at most one row

A scalar fullselect can return one value, or no row represented as NULL in its scalar context. If it returns multiple rows, Db2 can issue SQLCODE -811. Adding FETCH FIRST 1 ROW ONLY merely to suppress the error hides ambiguous data unless an ORDER BY defines which row is correct. Enforce a unique business key or aggregate deliberately.

A join can multiply rows

EXISTS returns each qualifying outer row once regardless of how many inner rows match. Replacing it with an ordinary inner join can repeat the outer row for every match. SELECT DISTINCT may hide the multiplication but adds work and can conceal a faulty join. Use a semijoin-style EXISTS, join to a pre-deduplicated key set, or group at the correct business grain.

NULL changes three-valued logic

Equality does not match NULL to NULL because NULL = NULL is UNKNOWN. A correlated predicate on nullable keys therefore needs a defined business rule. NOT IN is especially risky: one NULL returned by its subquery can make every comparison UNKNOWN. NOT EXISTS avoids that particular trap, but nullable correlation values and explicit IS NULL logic still require review. Never assume COALESCE is harmless; replacing NULL with a sentinel can collide with real data and affect indexability.

Use EXPLAIN to test the hypothesis

Run EXPLAIN for the original and rewritten statements under comparable conditions: current statistics, the same Db2 function level, bind options, special registers, and representative parameter assumptions. Query text alone cannot prove repeated execution or improvement. Visual EXPLAIN tools and Db2 explain tables can show whether Db2 transformed a subquery, selected an index, estimated many probes, introduced a sort, or chose a different join sequence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
EXPLAIN PLAN SET QUERYNO = 9410 FOR
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE EXISTS
       (SELECT 1
          FROM INVOICE AS I
         WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
           AND I.PAYMENT_STATUS = 'UNPAID');
 
SELECT QUERYNO,
       PLANNO,
       METHOD,
       ACCESSTYPE,
       MATCHCOLS,
       ACCESSCREATOR,
       ACCESSNAME,
       INDEXONLY
  FROM PLAN_TABLE
 WHERE QUERYNO = 9410
 ORDER BY PLANNO;

Do not optimize for one PLAN_TABLE field. More MATCHCOLS can be useful, but total cost, cardinality, join order, index-only access, sorts, prefetch, and data-page access also matter. After EXPLAIN, measure the statement in a representative environment. Compare returned rows first, then CPU, elapsed time, getpages, synchronous reads, sort activity, outer rows, inner probe frequency, and executions per business transaction.

A safe rewrite checklist

• State whether the requirement is existence, one scalar value, an aggregate, or rows.
• Record the expected result for no match, one match, duplicates, NULLs, and ties.
• Estimate how many outer rows reach the correlated block in real executions.
• Inspect index leading keys, predicate forms, data types, and catalog statistics.
• Rewrite with EXISTS, a pre-aggregated join, or a window function only when semantics match.
• EXPLAIN both forms under comparable settings and representative values.
• Run result-equivalence tests before comparing runtime resource use.
• Include index maintenance and statement frequency in the final cost decision.

Explain It Like I'm Five

Imagine a teacher has 1,000 student cards and asks, for each card, “Does this student have an overdue book?” Walking to the library and searching every shelf 1,000 times is the expensive correlated pattern. A good index is like a shelf list arranged by student, so each check is quick. EXISTS means the teacher stops as soon as one overdue book is found. A CTE and join are like asking the librarian to make one overdue-student list, then matching all cards against it. Neither method always wins: for two students, quick lookups may be best; for every student, one prepared list may be better. EXPLAIN shows which method Db2 plans to use.

Exercises

• Rewrite a correlated COUNT(*) > 0 test for open claims using EXISTS. Explain why duplicate claims do not alter the answer.
• Rewrite a correlated SUM of order amount as a grouped CTE and LEFT JOIN. Test a customer with no orders and decide whether NULL or zero is correct.
• Build a latest-status query with ROW_NUMBER. Add a unique tie-breaker and describe how the result would differ if all tied latest rows were required.
• Given an index on (ACCOUNT_ID, ALERT_STATUS, CREATED_TS), identify which columns can support a correlated EXISTS probe for open alerts and which can support ordering.
• Demonstrate how replacing EXISTS with an inner join multiplies one customer row when three invoices match. Repair the rewrite without adding an unexplained DISTINCT.
• EXPLAIN original and rewritten SQL. Record ACCESSTYPE, ACCESSNAME, MATCHCOLS, join sequence, estimated cardinality, sorts, and estimated cost, then design a runtime test.