Ways to filter the number of returned rows
A variety of different comparison operators in the predicate of a WHERE clause let you filter the number of returned rows.
You can use a WHERE clause to select the rows that are of interest to you. For example, suppose you want to select only the rows that represent the employees who earn a salary greater than $40 000. A WHERE clause specifies a search condition. A search condition is the criteria that DB2® uses to select rows. For any given row, the result of a search condition is true, false, or unknown. If the search condition evaluates to true, the row qualifies for additional processing. In other words, that row can become a row of the result table that the query returns. If the condition evaluates to false or unknown, the row does not qualify for additional processing.
A search condition consists of one or more predicates that are combined through the use of the logical operators AND, OR, and NOT. An individual predicate specifies a test that you want DB2 to apply to each row, for example, SALARY> 40000. When DB2 evaluates a predicate for a row, it evaluates to true, false, or unknown. Results are unknown only if a value (called an operand) of the predicate is null. If a particular employee's salary is not known (and is set to null), the result of the predicate SALARY> 40000 is unknown.
You can use a variety of different comparison operators in the predicate of a WHERE clause, as shown in the following table.
| Type of
comparison |
Specified with... | Example of predicate with comparison |
|---|---|---|
| Equal to null | IS NULL | COMM IS NULL |
| Equal to | = | DEPTNO = 'X01' |
| Not equal to | <> | DEPTNO <> 'X01' |
| Less than | < | AVG(SALARY) < 30000 |
| Less than or equal to | <= | SALARY <= 50000 |
| Greater than | > | SALARY> 25000 |
| Greater than or equal to | >= | SALARY>= 50000 |
| Similar to another value | LIKE | NAME LIKE ' or STATUS LIKE 'N_' |
| At least one of two predicates | OR | HIREDATE < '2000-01-01' OR SALARY < 40000 |
| Both of two predicates | AND | HIREDATE < '2000-01-01' AND SALARY < 40000 |
| Between two values | BETWEEN | SALARY BETWEEN 20000 AND 40000 |
| Equals a value in a set | IN (X, Y, Z) | DEPTNO IN ('B01', 'C01', 'D11') |
| Compares a value to another value | DISTINCT | value 1 IS DISTINCT from value 2 |
| Note: Another predicate, EXISTS, tests
for the existence of certain rows. The result of the predicate is
true if the result table that is returned by the subselect contains
at least one row. Otherwise, the result is false.
The XMLEXISTS predicate can be used to restrict the set of rows that a query returns, based on the values in XML columns. The XMLEXISTS predicate specifies an XPath expression. If the XPath expression returns an empty sequence, the value of the XMLEXISTS predicate is false. Otherwise, XMLEXISTS returns true. Rows that correspond to an XMLEXISTS value of true are returned. |
||
You can also search for rows that do not satisfy one of the predicates by using the NOT keyword before the specified predicate.