DB2 EXISTS and quantified predicates in depth: ANY, SOME and ALL

EXISTS and NOT EXISTS

EXISTS (fullselect) is TRUE when the number of rows from the fullselect is not zero, FALSE when it is zero. It is never UNKNOWN. The SELECT list is not exported. Nulls in inner columns do not make EXISTS unknown; they only affect whether inner predicates match.

1
2
3
4
5
6
7
SELECT E.EMPNO, E.LASTNAME
  FROM DSN8C10.EMP E
 WHERE EXISTS (
         SELECT 1
           FROM DSN8C10.PROJACT P
          WHERE P.EMPNO = E.EMPNO
       );

That is a correlated subquery: the inner WHERE references E.EMPNO from the outer row. For each employee, Db2 (or a rewrite) asks whether any project activity row exists. NOT EXISTS is the same subquery with NOT in front: employees with no activity rows.

Correlation versus a closed subquery

If the subquery does not reference the outer query, EXISTS is either true for every outer row or false for every outer row—usually a mistake (a “constant” filter). Correlation is what makes EXISTS a per-row existence test. You can also write a noncorrelated IN (SELECT EMPNO FROM PROJACT) for the same business question; that form builds a set of employee numbers first. Both can be correct.

SELECT 1, SELECT *, SELECT EMPNO

EXISTS does not return the SELECT list to the caller. SELECT 1 documents “I only care that a row qualifies.” SELECT * is legal and common in older code. Do not SELECT a LOB column “for fun” inside EXISTS; you still only need a row to exist, and LOB materialization helps nobody. A WHERE clause inside the subquery is what decides existence.

Semi-join versus DISTINCT join

EXISTS means “at least one match,” not “one output row per match.” A join to PROJACT without DISTINCT or GROUP BY duplicates employees who have two activities. EXISTS keeps one employee row. That is the usual reason to prefer EXISTS (or IN) over a raw join when the child table is not 1:1.

ANY and SOME

ANY and SOME are synonyms. expression operator ANY (fullselect) is TRUE if the comparison is true for at least one value the fullselect returns. It is FALSE if the fullselect is empty or if the comparison is false for every value and never true. If comparisons are UNKNOWN for some values and never TRUE, the quantified predicate is UNKNOWN.

1
2
3
4
5
6
7
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > ANY (
         SELECT SALARY
           FROM DSN8C10.EMP
          WHERE WORKDEPT = 'D11'
       );

That returns employees who earn more than at least one D11 employee (more than the D11 minimum, in effect, when salaries are non-null). = ANY is IN. <> ANY is true when there exists an element that differs—almost always true once the set has two different values, so it is a blunt instrument.

ALL

expression operator ALL (fullselect) is TRUE if the fullselect is empty or the comparison is true for every returned value. It is FALSE if the comparison is false for at least one value. UNKNOWN appears when no comparison is false but some are unknown (nulls in the set) and not all are true.

1
2
3
4
5
6
7
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > ALL (
         SELECT SALARY
           FROM DSN8C10.EMP
          WHERE WORKDEPT = 'E21'
       );

SALARY > ALL (D11 salaries) means strictly greater than every D11 salary—greater than the D11 maximum when the set is non-null. If E21 has no employees, > ALL is TRUE for every outer row (vacuous truth). That shocks people who expected “greater than a max that does not exist” to be false. Guard with EXISTS (SELECT 1 FROM EMP WHERE WORKDEPT = 'E21') AND SALARY > ALL (...) if the empty department should match nobody.

= ALL is almost never what you meant

COL = ALL (SELECT X FROM T) requires every X to equal COL. Two different X values make it FALSE. An empty T makes it TRUE. If you meant “COL is in the set,” write IN or = ANY. If you meant “all rows in T have this code,” write NOT EXISTS (SELECT 1 FROM T WHERE X IS DISTINCT FROM COL) or a grouped HAVING COUNT(DISTINCT X) = 1—depending on nulls.

NOT EXISTS as the safe anti-join

“Employees with no project activity”:

1
2
3
4
5
6
7
SELECT E.EMPNO
  FROM DSN8C10.EMP E
 WHERE NOT EXISTS (
         SELECT 1
           FROM DSN8C10.PROJACT P
          WHERE P.EMPNO = E.EMPNO
       );

Inner equality P.EMPNO = E.EMPNO does not match nulls. If a child row has a null EMPNO, it does not satisfy the correlation, so it does not make NOT EXISTS false for a parent. Contrast NOT IN (SELECT EMPNO FROM PROJACT): a single null EMPNO in PROJACT poisons the ALL comparison. That is the practical depth behind “prefer NOT EXISTS.”

Relational division (“employees who worked on every project in a set”) is often expressed with double NOT EXISTS: there does not exist a required project for which there does not exist an activity row. That pattern is easier to get right than nested ALL with nullable keys.

Quantified predicates and nulls

Left-side null: SALARY > ALL (...) is UNKNOWN when SALARY is null (except the empty ALL case, which is TRUE even when the left is null—because ALL does not evaluate the comparison against elements). Verify empty-set-plus-null-left on your function level if a CHECK or WHERE depends on it; the IBM rule “ALL is true if the fullselect is empty” is the documented short circuit.

Right-side nulls: > ALL of a set that contains 10, 20, and NULL cannot be TRUE, because 20 > NULL is UNKNOWN, so you cannot confirm every element satisfies the comparison. It may be UNKNOWN rather than FALSE. Filter the subquery with IS NOT NULL when you mean “greater than every known value.”

Access path notes

Correlated EXISTS with an index on the inner correlation columns is often a nested-loop semi-join: probe, find one row, stop. Missing that index, Db2 may scan or rewrite to a hash/merge semi-join. IN (noncorrelated subquery) may materialize the set. Do not add extra DISTINCT inside EXISTS; it does not change existence and can add a sort. Check EXPLAIN rather than rewriting blindly between EXISTS, IN, and JOIN—the cheapest form is data-dependent.

IBM’s predicate-processing tables treat EXISTS (subquery) and several quantified forms as stage 1 not always matching. They are not a substitute for COL = :hv on a leading index when you have a simple equality.

Explain It Like I'm Five

EXISTS is looking through a window into another classroom and asking “is anybody in there?” You do not need a roll call. NOT EXISTS is “the room is empty.” ANY/SOME is “is at least one kid taller than me?” ALL is “is every kid shorter than me?” If that classroom has zero kids, “every kid is shorter than me” is a funny teacher trick that counts as yes (nobody broke the rule). That is why empty ALL is TRUE. If you wanted “is my name on the attendance list?” that is IN or = ANY, not = ALL.

Exercises

1. Write EXISTS and IN versions of “employees who appear in PROJACT.” Compare row counts.
2. Using SYSIBM.SYSDUMMY1, evaluate 1 > ALL (SELECT 1 FROM SYSIBM.SYSDUMMY1 WHERE 1=0) and 1 > ANY (same empty subquery). Explain the two results.
3. Demonstrate the NOT IN null trap with a UNION ALL of a real EMPNO and a CAST(NULL AS CHAR(6)), then show NOT EXISTS returning the rows you expected.
4. Rewrite SALARY > ALL (SELECT SALARY FROM EMP WHERE WORKDEPT = 'D11') using a MAX subquery. When do they differ if D11 salaries can be null?
5. Explain why joining EMP to PROJACT without DISTINCT can multiply employees, and how EXISTS avoids that.