EXISTS and quantified predicates in DB2 SQL

EXISTS

The EXISTS predicate tests for the existence of certain rows. The fullselect can specify any number of columns. The values returned are ignored—IBM recommends SELECT * for convenience. The outer SELECT list of that fullselect must not contain an array value.

• True — the fullselect returns a non-zero number of rows.
• False — the fullselect returns zero rows.
• Cannot be unknown — even if the subquery’s columns are all null, a row still exists.

1
2
3
4
5
6
7
8
SELECT EMPNO
  FROM DSN8C10.EMP X
 WHERE EXISTS (
         SELECT *
           FROM DSN8C10.EMP
          WHERE X.WORKDEPT = WORKDEPT
            AND SALARY < 20000
       );

IBM’s example lists employee numbers of everyone who works in a department where at least one employee has a salary less than 20000. Like many EXISTS predicates, this one is correlated: the inner query refers to X.WORKDEPT from the outer row.

Correlation is not required. An uncorrelated EXISTS is a yes/no about a set that does not depend on the outer row (for example “does table T have any row at all?”). Correlated EXISTS is the usual “does this parent have a matching child?” pattern.

NOT EXISTS

Unlike NULL, LIKE, and IN, EXISTS has no form that contains the word NOT inside the predicate. To negate it, precede EXISTS with the logical operator NOT:

1
2
3
4
5
6
7
SELECT D.DEPTNO, D.DEPTNAME
  FROM DSN8C10.DEPT D
 WHERE NOT EXISTS (
         SELECT *
           FROM DSN8C10.EMP E
          WHERE E.WORKDEPT = D.DEPTNO
       );

NOT EXISTS is false when EXISTS is true, and true when EXISTS is false. Because EXISTS itself is never UNKNOWN, NOT EXISTS is also never UNKNOWN. That is why it is the safe rewrite of NOT IN (SELECT nullable_col …) for anti-joins.

Write the inner predicate as an equality on the keys you care about. Do not add extra OR IS NULL inside the subquery unless that is the business rule—those extra rows would make EXISTS true and hide the department.

EXISTS versus IN

• IN — membership of a value in a set of column values. Needs one column (or a row-value IN). Nulls in the set poison NOT IN.
• EXISTS — at least one row satisfies the inner search condition. Select list is ignored. Null-safe for “matching row?” questions.
• Use EXISTS when the question is existence, especially with extra inner predicates (dates, status codes) or nullable keys.
• Use IN when the set is a short list of constants or a single-column lookup of non-null codes.

The optimizer may rewrite either form as a join. You still write the form that matches the question and stays correct when a column can be null.

Quantified predicates: ANY, SOME, and ALL

A quantified predicate compares a value or a row with a collection of values. The collection is a fullselect. When you specify a single expression, the fullselect must return one column and any number of values (null or not). When you specify a row-value-expression, the fullselect must return the same number of columns.

ANY and SOME

SOME and ANY are synonyms. The predicate is:

• True if the relationship is true for at least one value (or row).
• False if the fullselect is empty or the relationship is false for every value.
• Unknown if it is not true for any value and at least one comparison is unknown because of a null.

1
2
3
4
5
6
7
8
9
10
11
-- Employees who earn more than at least one employee in E11
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > ANY (
         SELECT SALARY
           FROM DSN8C10.EMP
          WHERE WORKDEPT = 'E11'
       );
 
-- Same idea as IN
WHERE WORKDEPT = ANY (SELECT DEPTNO FROM DSN8C10.DEPT WHERE LOCATION = 'DALLAS');

IBM maps expression IN (fullselect) to expression = ANY (fullselect) and NOT IN to <> ALL.

ALL

ALL requires the relationship to hold for every returned value:

• True if the fullselect is empty or the relationship is true for every value.
• False if the relationship is false for at least one value.
• Unknown if it is not false for any value and at least one comparison is unknown because of a null.

1
2
3
4
5
6
7
8
-- Salary greater than every salary in department E11
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > ALL (
         SELECT SALARY
           FROM DSN8C10.EMP
          WHERE WORKDEPT = 'E11'
       );

Two beginner shocks:

• Empty department — > ALL of an empty set is TRUE (vacuous truth). Every employee would “earn more than everyone in a department that does not exist.” Filter that case with EXISTS if it is not what you meant.
• Null in the set — SALARY > ALL (… including a null salary) cannot be TRUE, because SALARY > NULL is UNKNOWN. Same family of bugs as NOT IN.

Other comparison operators work the same way: <, <=, >=, <>. Prefer <> over not-sign spellings in new SQL.

Row-value quantified predicates

You can compare a row to a set of rows: (WORKDEPT, JOB) = ANY (SELECT …) or <> ALL. Column counts and types must match. This is the quantified form of row-value IN.

Performance notes

EXISTS often becomes a semi-join: Db2 can stop at the first matching inner row. Correlated EXISTS with a supporting index on the inner join columns is a classic good pattern. Quantified predicates with noncorrelated subqueries may be merged or materialized. Wrapping the outer column in a function still hurts matching. For “no children,” compare EXPLAIN of NOT EXISTS versus a LEFT JOIN … WHERE key IS NULL; both are valid anti-joins.

Explain It Like I'm Five

EXISTS is asking “is there at least one cookie in the jar?” You do not care which cookie or how many—only whether the jar is empty. NOT EXISTS is “the jar is empty.” ANY/SOME is “is my cookie bigger than at least one cookie in that other jar?” ALL is “is my cookie bigger than every cookie in that jar?” If the other jar has no cookies, “bigger than every cookie” is a silly yes. If someone put a mystery unlabeled cookie (NULL) in the jar, “bigger than every cookie” cannot be a clear yes.

Exercises

1. Write EXISTS that keeps departments that have at least one employee with JOB = 'MANAGER'.
2. Write NOT EXISTS for projects with no activities in EMPPROJACT (or a child table you know).
3. Rewrite WORKDEPT IN (SELECT DEPTNO FROM DEPT) as = ANY.
4. Explain what SALARY > ALL (SELECT SALARY FROM EMP WHERE WORKDEPT = 'ZZZ') returns if no employee is in ZZZ.
5. Show why NOT IN (SELECT COMM FROM EMP) is unsafe and write NOT EXISTS instead for “employees whose commission is not in the set of positive commissions.”

Frequently asked questions

Should the subquery SELECT 1 or SELECT *?

IBM’s EXISTS discussion says the values are ignored and SELECT * is convenient. SELECT 1 is a common style and is also fine. Do not SELECT a huge LOB “for EXISTS”—the engine should not need the value, but you should not tempt it.

Can I use EXISTS in the SELECT list?

EXISTS is a predicate, not a scalar. In a select list use a CASE: CASE WHEN EXISTS (…) THEN 'Y' ELSE 'N' END, or a scalar subquery that returns a count.

Is = ALL useful?

COL = ALL (set) is true when every value in the set equals COL (or the set is empty). If the set has two different values, it is false. That is rarely a business question; = ANY or a join is more common.