Predicates overview in DB2 SQL

What a predicate is

A predicate is a SQL condition that tests data. Combined with AND, OR, and NOT, predicates form a search condition. The WHERE clause keeps rows for which that search condition is TRUE. HAVING does the same for groups after aggregation. JOIN ON clauses use predicates to decide which row combinations match.

1
2
3
4
5
SELECT EMPNO, LASTNAME, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
   AND SALARY > 50000
   AND COMM IS NOT NULL;

Each of WORKDEPT = 'A00', SALARY > 50000, and COMM IS NOT NULL is a predicate. AND combines them into one search condition. Because of three-valued logic, a null SALARY makes SALARY > 50000 UNKNOWN, and that row drops out unless you handle nulls explicitly.

Predicates are not the same as expressions. An expression computes a value (SALARY * 1.03). A predicate asks a true/false/unknown question about values. You often put expressions inside predicates: SALARY * 1.03 > 60000.

Where predicates show up

• WHERE — filter rows of a FROM result
• HAVING — filter groups after GROUP BY
• ON — join matching rules
• CASE WHEN … — searched CASE chooses branches with predicates
• CHECK constraints — row must satisfy predicates on insert/update

Beginners usually meet predicates first in WHERE. Everything you learn there transfers to the other contexts with only small differences in when they run in the query pipeline.

Kinds of predicates

Basic predicates

A basic predicate compares two expressions (or row-value expressions) with =, <>, <, >, <=, or >=. Older not-equal spellings like != or ¬= exist for compatibility; prefer <> in new SQL. If either side is null (or a scalar subquery is empty where rules say so), the result is UNKNOWN.

1
2
3
WHERE EMPNO = '528671'
WHERE SALARY < 20000
WHERE SALARY >= (SELECT AVG(SALARY) FROM HR.EMPLOYEE)

BETWEEN, IN, and LIKE

BETWEEN tests an inclusive range. IN tests membership in a list or the result of a subquery. LIKE matches character patterns: % means any string, _ means any single character (with escape options when you need literal % or _).

1
2
3
WHERE HIREDATE BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
WHERE WORKDEPT IN ('A00', 'B01', 'C01')
WHERE LASTNAME LIKE 'JO%N_';

NULL, EXISTS, DISTINCT, and friends

Use IS NULL / IS NOT NULL to test nulls—never = NULL. EXISTS is true when a subquery returns at least one row (often more efficient than counting). IS DISTINCT FROM / IS NOT DISTINCT FROM compare with null-aware equality. Quantified predicates (= ANY, > ALL, and similar) compare a value to a set from a subquery. Specialized predicates such as XMLEXISTS and ARRAY_EXISTS appear when you work with XML or arrays.

Combining predicates

AND, OR, and NOT combine predicates. Parentheses control grouping. Remember three-valued rules: FALSE AND anything is FALSE; TRUE OR anything is TRUE; NOT UNKNOWN is UNKNOWN.

1
2
3
WHERE (WORKDEPT = 'A00' OR WORKDEPT = 'B01')
  AND NOT (SALARY IS NULL)
  AND (COMM > 1000 OR BONUS > 500);

Write the most selective, indexed predicates clearly. Avoid wrapping columns in functions when a plain comparison would do—YEAR(HIREDATE) = 2020 can prevent index use that HIREDATE BETWEEN … would allow. Performance tuning is a later topic; the habit of writing direct predicates starts here.

Predicates and the optimizer (preview)

Db2’s optimizer reads your predicates when choosing an access path. Matching predicates on leading index columns, stage-1 versus stage-2 evaluation, and residual predicates all affect CPU and I/O. For this fundamentals page, remember: predicates are how you tell Db2 which rows matter, and clear predicates give the optimizer clearer choices.

Explain It Like I'm Five

A predicate is a yes/no question about each toy in a toy box: “Is it red?” “Is it bigger than this ball?” “Is the sticker missing?” Db2 walks the box and keeps only the toys where the answer is clearly yes. If the answer is “I can’t tell” (NULL / UNKNOWN), that toy does not stay in the keep pile. BETWEEN asks “is it between these two sizes?” LIKE asks “does the name look like this pattern?” EXISTS asks “is there at least one matching toy in another box?”

Exercises

1. Label each part of this WHERE as a predicate kind: WORKDEPT IN ('A00') AND SALARY BETWEEN 30000 AND 90000 AND LASTNAME LIKE 'A%' AND COMM IS NULL.
2. Rewrite WHERE COL = NULL into a correct null test.
3. Write an EXISTS subquery that keeps departments that have at least one employee.
4. Explain why a row with null SALARY does not appear for WHERE SALARY > 0.
5. Combine two basic predicates with OR and wrap them in parentheses with an AND condition.