Logical operators in DB2 SQL: AND, OR, and NOT

What a search condition is

A predicate is a single test, such as WORKDEPT = 'A00' or SALARY > 50000. A search condition is one or more predicates combined with AND, OR, and NOT. The search condition is TRUE, FALSE, or UNKNOWN about a given row. If you write only one predicate, the search condition is just that predicate.

Search conditions appear in WHERE, HAVING, join ON clauses, CASE WHEN, MERGE, searched UPDATE and DELETE, and check constraints. The same truth tables apply everywhere. The most common beginner home is WHERE:

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

WHERE keeps only rows for which the search condition is TRUE. FALSE is rejected. UNKNOWN is also rejected. That last rule is the source of almost every “my OR/AND with nulls lost rows” bug.

AND

AND means both sides must be TRUE. Use AND when every listed test must succeed for the row to qualify.

• TRUE AND TRUE — TRUE (keep the row)
• TRUE AND FALSE — FALSE (drop the row)
• FALSE AND anything — FALSE. One FALSE operand is enough. Db2 may skip evaluating the other operand.
• TRUE AND UNKNOWN — UNKNOWN (drop the row in WHERE)
• UNKNOWN AND UNKNOWN — UNKNOWN

Practical reading: AND is strict. A null in one compared column can turn a whole AND chain UNKNOWN even when the other tests look fine. If COMM is null, SALARY > 50000 AND COMM > 1000 is UNKNOWN for that row, not TRUE.

1
2
3
4
5
6
7
8
9
10
11
-- Both tests must be TRUE
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
   AND JOB = 'MANAGER';
 
-- Null COMM makes the second predicate UNKNOWN
SELECT EMPNO
  FROM HR.EMPLOYEE
 WHERE SALARY > 50000
   AND COMM > 1000;

If “missing commission” should still qualify when salary is high, say so explicitly with COALESCE or an extra OR IS NULL test. Do not hope AND will treat null as zero.

OR

OR means at least one side must be TRUE. Use OR when any of several tests is enough.

• TRUE OR anything — TRUE. One TRUE operand is enough, even if the other is UNKNOWN.
• FALSE OR FALSE — FALSE
• FALSE OR UNKNOWN — UNKNOWN (WHERE drops the row)
• UNKNOWN OR UNKNOWN — UNKNOWN

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

That OR is often rewritten as WORKDEPT IN ('A00', 'B01'), which is easier to read for a list of equalities. OR still matters when the tests are different columns or mixed predicate types:

1
2
3
4
5
SELECT EMPNO, LASTNAME, SALARY, BONUS
  FROM HR.EMPLOYEE
 WHERE SALARY > 80000
    OR BONUS > 5000
    OR JOB = 'PRES';

Beginners sometimes chain OR when they meant AND (“department A00 or salary over 50,000” vs “department A00 and salary over 50,000”). Read the English sentence out loud. If you need every test, use AND. If any test is enough, use OR.

NOT

NOT reverses a predicate or a parenthesized search condition.

• NOT TRUE is FALSE
• NOT FALSE is TRUE
• NOT UNKNOWN is UNKNOWN — negating “I don’t know” is still “I don’t know”

1
2
3
4
5
6
7
8
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE NOT WORKDEPT = 'A00';
 
-- Same idea with a comparison operator
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE WORKDEPT <> 'A00';

NOT WORKDEPT = 'A00' and WORKDEPT <> 'A00' are equivalent when WORKDEPT is not null. If WORKDEPT is null, both forms are UNKNOWN, so the row still disappears from WHERE. To include null departments as “not A00” you must add OR WORKDEPT IS NULL, or write WORKDEPT IS DISTINCT FROM 'A00'.

NOT with AND and OR

Placement of NOT and parentheses changes the meaning. IBM’s rule: to negate a set of predicates, enclose the whole set in parentheses and put NOT in front.

1
2
3
4
5
6
7
8
9
10
-- NOT applies only to the first predicate
SELECT EMPNO, SALARY, BONUS
  FROM HR.EMPLOYEE
 WHERE NOT SALARY >= 50000
   AND BONUS > 1000;
 
-- NOT applies to the whole group
SELECT EMPNO, SALARY, BONUS
  FROM HR.EMPLOYEE
 WHERE NOT (SALARY >= 50000 AND BONUS > 1000);

The first query keeps rows that fail the salary test and still have bonus over 1,000. The second keeps rows that fail the combined “high salary and high bonus” story—De Morgan’s law: NOT (P AND Q) is (NOT P) OR (NOT Q), with three-valued logic still in force.

NOT also appears inside specific predicates: NOT LIKE, NOT IN, NOT BETWEEN, NOT EXISTS, IS NOT NULL. Those are named predicate forms. They are not the same as wrapping an arbitrary AND/OR tree in NOT, but they follow the same TRUE/FALSE/UNKNOWN model. NOT IN with a null in the list is a classic trap (covered on the IN pages): the whole predicate can become UNKNOWN.

Precedence of logical operators

Search conditions inside parentheses are evaluated first. If you do not write parentheses:

• NOT is applied first
• AND is applied next
• OR is applied last

Operators at the same precedence level may be evaluated in any order. The SQL Reference says that order is undefined so Db2 can optimize. Do not write code that depends on AND clauses running left-to-right for side effects; SQL predicates should not rely on evaluation order.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Default: AND binds tighter than OR
-- Read as: A00, or (B01 and salary > 60000)
SELECT EMPNO, WORKDEPT, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
    OR WORKDEPT = 'B01'
   AND SALARY > 60000;
 
-- Force the grouping you actually meant
SELECT EMPNO, WORKDEPT, SALARY
  FROM HR.EMPLOYEE
 WHERE (WORKDEPT = 'A00' OR WORKDEPT = 'B01')
   AND SALARY > 60000;

Always parenthesize mixed AND/OR. Default precedence is correct SQL, but it is easy for a human to misread. Parentheses are free documentation.

Logical operators are not arithmetic

AND, OR, and NOT are not the same as the bit functions BITAND, BITOR, and BITNOT, and they are not numeric operators. You cannot write:

1
2
-- Wrong: AND does not add or combine numbers
-- WHERE SALARY AND BONUS > 50000

Write a predicate on each side: SALARY > 50000 AND BONUS > 1000. Bit flags stored in an INTEGER column use BITAND and friends (next operators page), then you still wrap that expression in a comparison to form a predicate that AND/OR can combine.

AND, OR, and indexes

How you combine predicates changes what the optimizer can do. A chain of AND equality and range tests on indexed columns is often indexable (sargable): Db2 can start at a matching key and stop early. A chain of OR predicates on different columns is harder. Db2 may use multiple index access (list prefetch / RID lists) and merge the row identifiers, or it may fall back to a table space scan when the OR is too messy.

1
2
3
4
5
6
7
-- Often friendly to a composite index on (WORKDEPT, SALARY)
WHERE WORKDEPT = 'A00'
  AND SALARY > 50000;
 
-- Two different columns: may need two indexes or a scan
WHERE WORKDEPT = 'A00'
   OR JOB = 'MANAGER';

Rewrites that preserve meaning can help: IN instead of many ORs on the same column, or UNION of two index-friendly SELECTs instead of a wide OR (measure with EXPLAIN; UNION has its own duplicate-elimination cost). Do not “optimize” by removing parentheses if that changes the answer. Correctness first, then EXPLAIN.

NOT and inequality (<>, NOT IN) are frequently not index-friendly. NOT (WORKDEPT = 'A00') still has to consider every other department—and still drops nulls. If the business rule is “every department except A00, including unknown,” say that with OR IS NULL. If the rule is “known departments other than A00,” the NOT form is fine and you accept the access path.

WHERE, ON, HAVING, and CASE

The same operators appear in several clauses. The truth tables do not change. The row that is being tested does.

• WHERE — filters rows after FROM (and after ON for the join’s intermediate result, depending on join type)
• ON — decides which pairs of rows match in a JOIN. Putting a filter in ON versus WHERE changes OUTER JOIN results
• HAVING — filters groups after GROUP BY. AND/OR/NOT apply to group-level predicates such as COUNT(*) > 1
• CASE WHEN — the WHEN search condition uses the same operators; UNKNOWN is treated like “this WHEN did not fire,” so later WHEN or ELSE runs

1
2
3
4
5
6
7
SELECT EMPNO,
       CASE
         WHEN SALARY >= 80000 AND JOB = 'MANAGER' THEN 'SENIOR'
         WHEN SALARY >= 80000 OR BONUS > 10000 THEN 'HIGH_PAY'
         ELSE 'OTHER'
       END AS PAY_BAND
  FROM HR.EMPLOYEE;

If SALARY is 90,000 and JOB is null, the first WHEN is UNKNOWN (because JOB = 'MANAGER' is UNKNOWN), so CASE does not take that branch. The second WHEN can still be TRUE from the salary test. Thinking in three-valued logic keeps CASE from looking “random.”

Common beginner mistakes

• Forgetting parentheses — OR plus AND without parentheses usually does not mean what the English sentence meant
• Treating UNKNOWN like FALSE in your head but like TRUE in the business rule — WHERE never keeps UNKNOWN
• NOT COL = value when nulls should count as “not that value” — add IS NULL or use IS DISTINCT FROM
• AND of mutually exclusive tests — WORKDEPT = 'A00' AND WORKDEPT = 'B01' is never TRUE for one row
• Using AND/OR on expressions instead of predicates

A useful debug habit: isolate each predicate with a SELECT COUNT(*), then combine them. If the combination is much smaller than you expected, look for AND that should have been OR, missing parentheses, or a nullable column.

Host-language programs see the same operators inside static SQL. COBOL does not evaluate AND/OR for you after the FETCH; Db2 already applied the search condition. If you need extra filtering in the program, that is a second pass on rows that already passed WHERE. Putting business rules in SQL AND/OR keeps the work set small and lets the optimizer use indexes. Putting them only in COBOL after SELECT * is how batch jobs read a million rows to keep ten.

When you copy predicates between WHERE and join ON, re-read OUTER JOIN semantics on the join pages. An AND filter in WHERE after a LEFT JOIN can turn that join into an inner join by dropping unmatched rows whose right-side columns are null. That is not a bug in AND; it is AND applied to the wrong clause.

Explain It Like I'm Five

Imagine a box of crayons. AND means “I want the crayon that is red AND fat.” Both stickers must match. OR means “red OR fat”—either sticker is enough. NOT means “not red.” If a crayon has no color sticker at all (NULL), you cannot honestly say it is red, and you also cannot honestly say it is not red. That “I don’t know” answer is UNKNOWN, and the WHERE box only keeps crayons whose whole story is a clear yes.

Exercises

1. Write a WHERE clause for employees in A00 whose salary is at least 60,000. Use AND.
2. Write a WHERE clause for employees in A00 or B01. Then rewrite it with IN.
3. Predict TRUE/FALSE/UNKNOWN for: (SALARY > 50000) AND (COMM > 0) when COMM is null. Does WHERE keep the row?
4. Add parentheses so “department A00 or B01, and job MANAGER” cannot be misread.
5. Rewrite NOT (JOB = 'CLERK' OR JOB = 'OPERATOR') as a pair of AND comparisons, then say what happens if JOB is null.