Filtering with AND, OR, and NOT in DB2

AND

AND connects two conditions. The combined result is TRUE only when both sides are TRUE. Use AND when the business rule is “all of these must hold.”

1
2
3
4
SELECT EMPNO, HIREDATE, SALARY
  FROM HR.EMPLOYEE
 WHERE HIREDATE < DATE('1998-01-01')
   AND SALARY < 35000;

An employee hired in 1990 who earns 20,000 qualifies. An employee hired in 1990 who earns 80,000 fails the salary test. An employee hired in 2020 who earns 20,000 fails the hire-date test. Both predicates must pass.

NULL changes the story. If SALARY is null, SALARY < 35000 is UNKNOWN. TRUE AND UNKNOWN is UNKNOWN, so WHERE discards the row even though the hire date passed. If you need “salary under 35,000 or salary not yet entered,” you must say so:

1
2
WHERE HIREDATE < DATE('1998-01-01')
  AND (SALARY < 35000 OR SALARY IS NULL)

AND is also how you stack join-style filters with local filters after an inner join. Each extra AND predicate, when it is a Boolean term (not trapped inside OR), can help the optimizer reject rows early. That does not mean you should AND nonsense; it means a clear list of required facts is both readable and friendly to access-path selection.

OR

OR connects two conditions. The combined result is TRUE when at least one side is TRUE. Use OR when the business rule is “any of these is enough.”

1
2
3
4
SELECT EMPNO, HIREDATE, SALARY
  FROM HR.EMPLOYEE
 WHERE HIREDATE < DATE('1998-01-01')
    OR SALARY < 35000;

Now the 1990 / 80,000 employee qualifies on hire date, and the 2020 / 20,000 employee qualifies on salary. Only people who fail both tests are out.

TRUE OR UNKNOWN is TRUE: if one side already succeeded, a null on the other side does not hurt. FALSE OR UNKNOWN is UNKNOWN: you still do not have a definite yes, so WHERE drops the row. That is why OR does not automatically “include the nulls.”

Long OR lists on one column are often clearer as IN:

1
2
3
4
5
6
7
-- Same idea, easier to maintain
WHERE WORKDEPT IN ('A00', 'B01', 'C01')
 
-- Equivalent OR chain
WHERE WORKDEPT = 'A00'
   OR WORKDEPT = 'B01'
   OR WORKDEPT = 'C01'

IN and a chain of equalities are not a free pass around nulls: a null WORKDEPT still fails WORKDEPT = 'A00' and is not in the IN list. Excessive OR across different columns can also make index matching harder; that is a later performance topic, but it is why shops prefer AND of selective predicates when the rule allows it.

NOT

NOT negates a predicate or a parenthesized search condition. NOT TRUE is FALSE. NOT FALSE is TRUE. NOT UNKNOWN is still UNKNOWN—the NOT operator has no effect on an unknown condition. You cannot “NOT” your way into knowing a null.

1
2
3
4
5
6
7
8
9
10
-- NOT applies only to the salary predicate
SELECT EMPNO, EDLEVEL, JOB
  FROM HR.EMPLOYEE
 WHERE NOT (SALARY >= 50000)
   AND EDLEVEL < 18;
 
-- NOT applies to the whole parenthesized pair
SELECT EMPNO, EDLEVEL, JOB
  FROM HR.EMPLOYEE
 WHERE NOT (SALARY > 50000 AND EDLEVEL > 18);

In the first statement, NOT (SALARY >= 50000) is the same idea as SALARY < 50000 for non-null salaries, and still UNKNOWN when SALARY is null. The AND then requires EDLEVEL < 18 as well. In the second statement, you keep rows that fail the combined “high salary and high education” test—including people who fail only one of those two facts. Parentheses are not decoration; they choose the question.

Many predicates have a built-in negative form that is easier to read than NOT plus the positive form: NOT LIKE versus NOT (x LIKE …), NOT IN, NOT BETWEEN, NOT EXISTS, IS NOT NULL. Prefer the dedicated form when it exists. Remember NOT IN and nulls: if the IN list or subquery can produce NULL, NOT IN becomes a trap because comparisons with NULL are UNKNOWN. NOT EXISTS is often safer than NOT IN for subquery exclusion.

Predicate precedence

Search conditions inside parentheses are evaluated first. If you do not write parentheses, Db2 applies NOT before AND, and AND before OR. Operators at the same precedence level may be evaluated in either order so the optimizer can rearrange work. That last point matters: do not assume left-to-right OR as if SQL were a programming language with short-circuit side effects. Predicates should be free of surprises if Db2 swaps two ORs.

1
2
3
4
5
6
7
8
-- AND binds tighter than OR: (salary and commission) OR bonus
WHERE SALARY > :SS AND COMM > :CC OR BONUS > :BB
 
-- Parentheses force OR first: salary AND (commission OR bonus)
WHERE SALARY > :SS AND (COMM > :CC OR BONUS > :BB)
 
-- Extra OR at the same level; either OR order is allowed
WHERE SALARY > :SS AND COMM > :CC OR BONUS > :BB OR SEX = :GG

IBM’s own examples match those shapes. If you move the parentheses, the meaning of the WHERE clause can change completely. When AND and OR appear in the same condition, write parentheses even when you know the default. The next reader (including you in six months) should not have to recall the precedence table under pressure.

NOT also follows the same rule. NOT SALARY > :SS AND COMM > :CC means (NOT SALARY > :SS) AND COMM > :CC. NOT (SALARY > :SS AND COMM > :CC) negates the pair. Moving NOT without moving parentheses is one of the most common production bugs in handwritten SQL.

Truth table (including UNKNOWN)

WHERE and HAVING keep a row or group only when the whole search condition is TRUE. Scan the UNKNOWN rows in the table: many of them are not TRUE, so they disappear. The only “helpful” UNKNOWN cases are the ones OR can rescue with a TRUE on the other side, and the ones AND can kill early with a FALSE on the other side.

Common beginner mistakes

• Forgetting parentheses when mixing AND and OR. WORKDEPT = 'A00' OR JOB = 'CLERK' AND SALARY > 50000 is not “A00 or clerks over 50k.” AND runs first, so it is A00 (any salary) or (clerks over 50k).
• Writing WHERE COL = NULL. Use IS NULL. Equality with NULL is UNKNOWN, so the filter keeps nobody.
• Expecting NOT to include nulls. NOT (SALARY > 50000) does not keep null salaries. Add OR SALARY IS NULL if that is the business rule.
• NOT IN with a subquery that can return NULL. One null in the list can make every NOT IN test UNKNOWN. Prefer NOT EXISTS when exclusion must be robust.
• AND of mutually exclusive equals on one column. WORKDEPT = 'A00' AND WORKDEPT = 'B01' is never TRUE. You wanted OR or IN.
• Putting outer-join filters in WHERE instead of ON. A predicate on the null-supplying side in WHERE turns a left join into an inner join for those rows.
• Assuming left-to-right short circuit. Same-level ORs may be reordered. Do not hide required logic in “the first OR must run first.”

1
2
3
4
5
6
7
8
9
10
11
12
-- Mistake: AND of two equals on the same column
WHERE WORKDEPT = 'A00' AND WORKDEPT = 'B01'   -- empty result
 
-- Intended: either department
WHERE WORKDEPT IN ('A00', 'B01')
 
-- Mistake: AND/OR without parentheses
WHERE WORKDEPT = 'A00' OR JOB = 'CLERK' AND SALARY > 50000
 
-- Intended: department A00, or well-paid clerks
WHERE WORKDEPT = 'A00'
   OR (JOB = 'CLERK' AND SALARY > 50000)

Explain It Like I'm Five

AND is “you must have a ticket and a hat.” Miss either one and you stay outside. OR is “ticket or hat—one is enough.” NOT is “anyone who is not wearing a hat.” If someone’s hat is in a closed box and you cannot see it, you do not know whether they have a hat, so “not wearing a hat” is not a yes. Parentheses are like putting two friends in a huddle: you decide whether the huddle as a whole is allowed in, instead of checking each friend separately.

Exercises

1. Write a WHERE clause for employees in A00 or B01 who earn at least 40000. Use parentheses so the salary test applies to both departments.
2. Show two different meanings of NOT SALARY > 50000 AND EDLEVEL > 16 versus NOT (SALARY > 50000 AND EDLEVEL > 16). Which rows differ?
3. Fill in P AND Q and P OR Q when P is TRUE and Q is UNKNOWN. Does WHERE keep the row in each case?
4. Rewrite a three-way OR of WORKDEPT equalities as IN. Then add “or WORKDEPT is null” correctly.
5. Explain why WHERE JOB <> 'PRES' does not return rows where JOB is NULL, and write a condition that does if that is required.