THE MIDNIGHT CODE REVIEW / CASE 01
The orders that
never came back.
Five orders. Two customers on hold. A query meant to find the rest.
Read it like a code review. What does it actually return?
The query under review
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM holds h
WHERE h.customer_id = customer_id
)
ORDER BY o.order_id;holds.A nudge, without the answer
Read the equality inside the subquery. Which table supplies each side? Do not assume that an unqualified name refers to the outer query.
THE REVEAL
No orders. No mystery.
The bare customer_id is found in the inner scope, where holds h already has that column. The condition behaves as:
WHERE h.customer_id = h.customer_idBoth hold rows satisfy that comparison because their values are non-null. The subquery returns rows for every order, so NOT EXISTS is false for every order. The expected result is zero rows.
- Start with order 101. Its customer is 1, but the inner comparison never references that customer.
- Inspect the hold rows. 2 = 2 and 4 = 4 are true.
- Apply NOT EXISTS. Matching rows exist, so order 101 is excluded. The same reasoning applies to all five orders.
Qualify the outer query’s customer.
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM holds h
WHERE h.customer_id = o.customer_id
)
ORDER BY o.order_id;o.customer_id explicitly refers to the order being evaluated. The corrected query should return 101, 103, and 105.
Qualify column references in subqueries, and use distinct aliases for each scope. Read both sides of an equality aloud: “hold customer equals order customer.”
Try the complete, read-only example.
The download includes both queries. It creates no tables and changes no business data. Run each statement separately in ACS Run SQL Scripts and compare the results.
Download the SQL fixture ↓Show the complete original query, including data
WITH
orders (order_id, customer_id) AS (
VALUES (101, 1), (102, 2), (103, 3), (104, 4), (105, 5)
),
holds (customer_id) AS (
VALUES (2), (4)
)
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM holds h
WHERE h.customer_id = customer_id
)
ORDER BY o.order_id;Validation status: This explanation follows IBM’s documented name-resolution and EXISTS rules. Execution of this exact fixture on IBM i is pending; the results above are expected results, not a captured IBM i run.
References: IBM: correlated references · IBM: EXISTS predicate
Would this pass your code review?
Send the question to a colleague. The shared text keeps the answer hidden.
Have a different result on IBM i? Send a correction with the query, release/PTF level, and result—please omit private business data.