Search Conditions

Search Conditions

A search condition specifies a condition that is "true" or "false" about a given row or group.

Following is the syntax diagram for a search condition:

   ┌─ choice ───────────────────────────┐
►►─▼─┬───────┬─┬─ predicate ──────────┬─┴─────────────────────────────────────►◄
     └─ NOT ─┘ └─ (search-condition) ─┘

Expansion of Where choice is as follows

├──┬─ AND ─┬───────────────────────────────────────────────────────────────────┤
   └─ OR ──┘

Description

predicate

Specify a predicate. For more information about predicates, see Predicates.

(search-condition)

Specify a search condition. Enclose the search condition within parentheses.

AND

A keyword. This Boolean operator indicates that both conditions joined by this keyword must be satisfied before the result is true.

OR

A keyword. This Boolean operator indicates that only one of the conditions joined by this keyword must be satisfied for the result to be true.

NOT

A keyword. Using NOT negates the result of the predicate or search condition.

The result of a search condition is derived by the application of the specified Boolean operators to the result of each specified predicate. If Boolean operators are not specified, the result of the search condition is the result of the specified predicate.

If the search condition or predicate is preceded by NOT, the result is negated. For example:

• NOT(true) means false
• NOT(false) means true

The following table shows the results when:

1. The value of predicate P is joined with the value of predicate Q by AND.
2. The value of predicate P is joined with the value of predicate Q by OR.
3. Each of the previous operations is preceded by NOT.

Boolean expressions within parentheses are evaluated first. When the order of evaluation is not specified by parentheses:

1. NOT is applied before AND.
2. AND is applied before OR.
3. Operators at the same precedence level are applied from left to right.

Examples

The following examples show search conditions in WHERE and HAVING clauses. For more information about these clauses, see SELECT.

Example 1: This search condition specifies that the value of PNUM must be P3, which is a literal.

 WHERE PNUM = 'P3'

Example 2: This search condition specifies that the value of NAME (a string) must be the same as in the host-variable, VAR2.

 WHERE NAME LIKE :VAR2

Example 3: This search conditions specifies that the city must be Dallas, and the salary must be greater than $20,000.

 WHERE CITY = 'DALLAS' AND SALARY > 20000

Example 4: This search condition uses an IN predicate and a subselect. The condition specifies that the employee number must be contained in the result table for the SELECT which retrieves only those employee numbers related to department E11.

 WHERE EMPNO IN (SELECT EMPNO
                 FROM EMP
                 WHERE DEPTNO = 'E11')

Example 5: This search condition uses a function and a subselect, which also includes a function. The condition specifies that the maximum salary (for some group) must be less than the average salary for all employees.

 HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                       FROM EMP))