The WHERE clause in DB2 SQL

What WHERE does

In a subselect, processing order is conceptually: FROM (and joins) produce rows, WHERE filters those rows, GROUP BY groups the survivors, HAVING filters groups, then SELECT projects columns and ORDER BY sorts. You do not have to memorize optimizer internals to use WHERE, but you do need this picture: WHERE never sees groups, and it never sorts. It only answers “does this row qualify?”

1
2
3
SELECT EMPNO, LASTNAME, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';

Db2 reads (or indexes into) HR.EMPLOYEE. For each candidate row it evaluates WORKDEPT = 'A00'. If the predicate is TRUE, the row can appear in the result. If WORKDEPT is 'B01', the predicate is FALSE and the row is dropped. If WORKDEPT is NULL, the predicate is UNKNOWN and the row is also dropped. WHERE is strict: only TRUE counts.

The same WHERE idea appears outside SELECT. A searched UPDATE or DELETE uses a search condition to choose which rows to change or remove. MERGE can use search conditions when matching source and target rows. Learning WHERE once pays off in every data-change statement you write.

WHERE predicates

A predicate is one test that is TRUE, FALSE, or UNKNOWN about a row. The WHERE clause is a search condition: one predicate, or several predicates combined with AND, OR, and NOT. Predicates are evaluated after the expressions that are their operands. Values in the same predicate must be compatible types (Db2 may cast when the rules allow it).

Common predicate kinds you will put in WHERE:

• Basic (comparison) — SALARY > 50000, WORKDEPT = 'A00'
• BETWEEN — SALARY BETWEEN 40000 AND 60000 (inclusive range)
• IN — WORKDEPT IN ('A00', 'B01') or IN (subquery)
• LIKE — LASTNAME LIKE 'S%' with % and _ wildcards
• NULL — COMM IS NULL or COMM IS NOT NULL
• EXISTS — EXISTS (SELECT 1 FROM …) true when the subquery returns a row
• Quantified — SALARY > ALL (subquery) or = ANY (subquery)

This page focuses on WHERE itself and on comparison predicates. LIKE, IN, EXISTS, and friends each deserve their own later pages. The predicates overview already maps the catalog of tests; here you learn where those tests sit in a query and how they decide which rows survive.

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

Three predicates, one search condition. AND means every predicate must be TRUE. A null COMM makes COMM IS NOT NULL FALSE, so that employee is excluded even if the department and salary look right. That is usually what you want when “has a commission” is part of the business question.

Comparison predicates

A basic predicate compares two expressions, or two row-value expressions (lists of expressions) with the same number of items. The portable comparison operators are:

Each operator asks a different question. Equal (=) is the usual join-style match and the usual “this code” filter. Not equal (<>) keeps everything except one value, but a NULL still yields UNKNOWN, so nulls are not “not equal” in the everyday English sense. Less than and greater than follow the data type’s comparison rules: numbers by magnitude, dates by calendar order, character strings by collating sequence and CCSID conversion rules. Less-or-equal and greater-or-equal include the boundary. BETWEEN is often clearer than a pair of >= and <= predicates, but it is the same idea.

If either operand is null, or a scalar subquery on one side returns no row, the comparison is UNKNOWN. If a scalar subquery returns more than one row, Db2 raises an error (typically SQLCODE -811). Write subqueries that are guaranteed to be scalar, or use IN / EXISTS instead.

1
2
3
4
5
6
7
8
9
10
11
-- Comparison with a literal
WHERE JOB = 'CLERK'
 
-- Comparison with an expression
WHERE SALARY + BONUS > 60000
 
-- Comparison with a scalar subquery
WHERE SALARY >= (SELECT AVG(SALARY) FROM HR.EMPLOYEE)
 
-- Row-value comparison (same number of items on each side)
WHERE (WORKDEPT, JOB) = ('A00', 'CLERK')

Prefer <> for “not equal.” Db2 still accepts !=, !<, !> in code pages where exclamation point is X'5A', and the older ¬=, ¬<, ¬> forms. Those extra spellings exist so old statements keep compiling. New SQL should use <>, <=, and >= so the statement survives character conversion when it moves between systems.

Character comparisons are not “just letters.” Trailing blanks in CHAR versus VARCHAR, mixed SBCS/DBCS data, and CCSID conversion can change whether two strings compare equal. If a filter that “looks right” returns nothing, check padding and encoding before rewriting the business logic. Numeric comparisons promote according to Db2 type-precedence rules; mixing DECIMAL and INTEGER is normal, mixing DECFLOAT can change whether a predicate is stage 1.

Combining predicates with AND and OR

AND means both sides must be TRUE. OR means at least one side must be TRUE. NOT reverses TRUE and FALSE and leaves UNKNOWN as UNKNOWN. Without parentheses, NOT is applied first, then AND, then OR. The next page in this series walks through precedence and beginner mistakes in depth; here is the minimum you need to write a correct WHERE.

1
2
3
4
5
6
7
8
9
10
11
-- Both must be true
WHERE HIREDATE < DATE('1998-01-01')
  AND SALARY < 35000
 
-- At least one must be true
WHERE HIREDATE < DATE('1998-01-01')
   OR SALARY < 35000
 
-- Parentheses change the meaning
WHERE (HIREDATE < DATE('1998-01-01') AND SALARY < 40000)
   OR EDLEVEL < 18

Truth tables include UNKNOWN, not only TRUE and FALSE. TRUE AND UNKNOWN is UNKNOWN. FALSE AND UNKNOWN is FALSE (the AND already failed). TRUE OR UNKNOWN is TRUE (the OR already succeeded). That is why a null in one AND-ed predicate can drop a row, while a null in one OR-ed predicate might still let the row through if the other side is TRUE.

WHERE compared with ON and HAVING

For an inner join, a local filter in ON and the same filter in WHERE usually mean the same rows. For a left outer join they do not. A predicate on the null-supplying table in WHERE runs after the join and will remove the unmatched preserved rows you asked the outer join to keep. Put join matching in ON; put “after the join, I still only want …” filters in WHERE, and be careful with columns that can be null-extended. HAVING is for aggregates: WHERE SALARY > 50000 filters employees; HAVING AVG(SALARY) > 50000 filters departments.

WHERE in UPDATE and DELETE

A searched UPDATE or DELETE without WHERE affects every row of the table (subject to permissions and constraints). That is almost never what you want in production. Always write a search condition that identifies the business key or a clearly bounded set.

1
2
3
4
5
6
7
UPDATE HR.EMPLOYEE
   SET SALARY = SALARY * 1.03
 WHERE WORKDEPT = 'A00'
   AND JOB <> 'PRES';
 
DELETE FROM HR.EMP_STAGE
 WHERE LOAD_DATE < CURRENT DATE - 30 DAYS;

In COBOL programs, prefer host variables over concatenated literals in dynamic SQL: WHERE EMPNO = :HV-EMPNO. Bind variables keep the statement reusable in the dynamic statement cache and avoid injection mistakes. Static SQL with host variables is the classic z/OS pattern.

Performance notes for beginners

The optimizer uses WHERE (and ON) predicates to choose an access path. Predicates on indexed columns that Db2 can evaluate early (stage 1, and better yet index matching) usually beat wrapping the column in a function. WHERE YEAR(HIREDATE) = 2019 cannot use a simple index on HIREDATE the way WHERE HIREDATE >= '2019-01-01' AND HIREDATE < '2020-01-01' can. Later performance pages cover stage 1 versus stage 2 in detail. For now: keep predicates simple, sargable, and honest about nulls.

A Boolean term is a predicate that, if FALSE, makes the whole WHERE FALSE. Those terms are especially useful for index access. Nested ORs that hide every column inside a big disjunction are harder for the optimizer. Prefer AND of clear tests when the business rule really is “all of these must be true.”

Explain It Like I'm Five

Imagine a giant box of employee cards. WHERE is the instruction “only keep the cards that pass this test.” The test might be “department is A00” or “salary is more than 50,000.” If a card is missing the salary sticker, the “more than 50,000” question cannot be answered yes or no—so that card is not kept. AND means every test on the card must pass. OR means one passing test is enough.

Exercises

1. Write a SELECT that lists EMPNO and LASTNAME for clerks in department D11 with salary at least 30000.
2. Explain what happens to a row whose SALARY is NULL in WHERE SALARY <> 0. How would you include those rows if the business wants “salary not zero, or salary unknown”?
3. Rewrite WHERE YEAR(HIREDATE) = 2018 as a range on HIREDATE that is friendlier to an index.
4. Write a searched UPDATE that adds 500 to BONUS for JOB = 'SALESREP' only. What goes wrong if you omit WHERE?
5. Compare WHERE WORKDEPT = 'A00' OR WORKDEPT = 'B01' with an IN list. Which is easier to read, and are they the same for null WORKDEPT?