Specifying search conditions with a WHERE clause
You can use a WHERE clause to select the rows that meet certain conditions. A WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate specifies a test that you want Db2 to apply to each table row.
About this task
A WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate specifies a test that you want Db2 to apply to each table row.
Procedure
To select specific data rows with a search condition:
Db2 evaluates a predicate for each row as true, false, or unknown. Results are unknown only if an operand is null.
If a search condition contains a column of a distinct type, the value to which that column is compared must be of the same distinct type, or you must cast the value to the distinct type.
The following table lists the type of comparison, the comparison operators, and an example of each type of comparison that you can use in a predicate in a WHERE clause.
| Type of comparison | Comparison operator | Example |
|---|---|---|
| Equal to | = | DEPTNO = 'X01' |
| Not equal to | <> | DEPTNO <> 'X01' |
| Less than | < | AVG(SALARY) < 30000 |
| Less than or equal to | <= | AGE <= 25 |
| Not less than | >= | AGE >= 21 |
| Greater than | > | SALARY > 2000 |
| Greater than or equal to | >= | SALARY >= 5000 |
| Not greater than | <= | SALARY <= 5000 |
| Equal to null | IS NULL | PHONENO IS NULL |
| Not equal to another value or one value is equal to null | IS DISTINCT FROM | PHONENO IS DISTINCT FROM :PHONEHV |
| Similar to another value | LIKE | NAME LIKE ' or STATUS LIKE 'N_' |
| At least one of two conditions | OR | HIREDATE < '1965-01-01' OR SALARY < 16000 |
| Both of two conditions | AND | HIREDATE < '1965-01-01' AND SALARY < 16000 |
| Between two values | BETWEEN | SALARY BETWEEN 20000 AND 40000 |
| Equals a value in a set | IN (X, Y, Z) | DEPTNO IN ('B01', 'C01', 'D01') |
|
Note: SALARY BETWEEN 20000 AND 40000 is equivalent to SALARY >= 20000 AND SALARY <= 40000.
|
||
You can also search for rows that do not satisfy one of the preceding conditions by using the NOT keyword before the specified condition.
You can search for rows that do not satisfy the IS DISTINCT FROM predicate by using either of the following predicates:
value 1 IS NOT DISTINCT FROM value 2NOT(value 1 IS DISTINCT FROM value 2)
Both of these forms of the predicate create an expression for which one value is equal to another value or both values are equal to null.