>>-expression----+- = --------+-----expression-----------------><
| (1) |
+- <> -------+
+- < --------+
+- > --------+
| (1) |
+- <= -------+
| (1) |
'- >= -------'
Notes:
|
A basic predicate compares two values.
If the value of either operand is null, the result of the predicate is unknown. Otherwise the result is either true or false.
For values x and y:
Examples:
EMPNO='528671' SALARY < 20000 PRSTAFF <> :VAR1 SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
>>-+-expression1--+- = -------+---+-SOME-+---(fullselect1)------+->
| | (1) | +-ANY--+ |
| +- <> ------+ '-ALL--' |
| +- < -------+ |
| +- > -------+ |
| +- <= ------+ |
| '- >= ------' |
| .-,----------------. |
| V | |
'-(------expression2----+---)-- = --+-SOME-+---(fullselect2)-'
'-ANY--'
>--------------------------------------------------------------><
Notes:
|
A quantified predicate compares a value or values with a collection of values.
The fullselect must identify a number of columns that is the same as the number of expressions specified to the left of the predicate operator (SQLSTATE 428C4). The fullselect may return any number of rows.
When ALL is specified:
When SOME or ANY is specified:
Examples: Use the following tables when referring to the following examples.
Example 1
SELECT COLA FROM TBLAB
WHERE COLA = ANY(SELECT COLX FROM TBLXY)
Results in 2,3. The subselect returns (2,3). COLA in rows 2 and 3 equals at least one of these values.
Example 2
SELECT COLA FROM TBLAB
WHERE COLA > ANY(SELECT COLX FROM TBLXY)
Results in 3,4. The subselect returns (2,3). COLA in rows 3 and 4 is greater than at least one of these values.
Example 3
SELECT COLA FROM TBLAB
WHERE COLA > ALL(SELECT COLX FROM TBLXY)
Results in 4. The subselect returns (2,3). COLA in row 4 is the only one that is greater than both these values.
Example 4
SELECT COLA FROM TBLAB
WHERE COLA > ALL(SELECT COLX FROM TBLXY
WHERE COLX<0)
Results in 1,2,3,4, null. The subselect returns no values. Thus, the predicate is true for all rows in TBLAB.
Example 5
SELECT * FROM TBLAB WHERE (COLA,COLB+10) = SOME (SELECT COLX, COLY FROM TBLXY)
The subselect returns all entries from TBLXY. The predicate is true for the subselect, hence the result is as follows:
COLA COLB
----------- -----------
2 12
3 13
Example 6
SELECT * FROM TBLAB WHERE (COLA,COLB) = ANY (SELECT COLX,COLY-10 FROM TBLXY)
The subselect returns COLX and COLY-10 from TBLXY. The predicate is true for the subselect, hence the result is as follows:
COLA COLB
----------- -----------
2 12
3 13
>>-expression----+------+--------------------------------------->
'-NOT--'
>----BETWEEN--expression--AND--expression----------------------><
|
The BETWEEN predicate compares a value with a range of values.
The BETWEEN predicate:
value1 BETWEEN value2 AND value3
is equivalent to the search condition:
value1 >= value2 AND value1 <= value3
The BETWEEN predicate:
value1 NOT BETWEEN value2 AND value3
is equivalent to the search condition:
NOT(value1 BETWEEN value2 AND value3); that is, value1 < value2 OR value1 > value3.
The values for the expressions in the BETWEEN predicate can have different code pages. The operands are converted as if the above equivalent search conditions were specified.
The first operand (expression) cannot include a function that is variant or has an external action (SQLSTATE 426804).
Given a mixture of datetime values and string representations of datetime values, all values are converted to the data type of the datetime operand.
Examples:
Example 1
EMPLOYEE.SALARY BETWEEN 20000 AND 40000
Results in all salaries between $20,000.00 and $40,000.00.
Example 2
SALARY NOT BETWEEN 20000 + :HV1 AND 40000
Assuming :HV1 is 5000, results in all salaries below $25,000.00 and above $40,000.00.
Example 3
Given the following:
| Expressions | Type | Code Page |
|---|---|---|
| HV_1 | host variable | 437 |
| HV_2 | host variable | 437 |
| Col_1 | column | 850 |
When evaluating the predicate:
:HV_1 BETWEEN :HV_2 AND COL_1
It will be interpreted as:
:HV_1 >= :HV_2 AND :HV_1 <= COL_1
The first occurrence of :HV_1 will remain in the application code page since it is being compared to :HV_2 which will also remain in the application code page. The second occurrence of :HV_1 will be converted to the database code page since it is being compared to a column value.
>>-EXISTS--(fullselect)---------------------------------------->< |
The EXISTS predicate tests for the existence of certain rows.
The fullselect may specify any number of columns, and
Example:
EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)
>>-+-expression1--+------+--IN----+-(fullselect1)----------------+-+->
| '-NOT--' | .-,----------------. | |
| | V | | |
| +-(------expression2----+---)--+ |
| '-expression2------------------' |
| .-,----------------. |
| V | |
'-(------expression3----+---)----+------+--IN----(fullselect2)--'
'-NOT--'
>--------------------------------------------------------------><
|
The IN predicate compares a value or values with a collection of values.
The fullselect must identify a number of columns that is the same as the number of expressions specified to the left of the IN keyword (SQLSTATE 428C4). The fullselect may return any number of rows.
expression IN expressionis equivalent to a basic predicate of the form:
expression = expression
expression IN (fullselect)is equivalent to a quantified predicate of the form:
expression = ANY (fullselect)
expression NOT IN (fullselect)is equivalent to a quantified predicate of the form:
expression <> ALL (fullselect)
expression IN (expressiona, expressionb, ..., expressionk)is equivalent to:
expression = ANY (fullselect)where fullselect in the values-clause form is:
VALUES (expressiona), (expressionb), ..., (expressionk)
(expressiona, expressionb,..., expressionk) IN (fullselect)is equivalent to a quantified predicate of the form:
(expressiona, expressionb,..., expressionk) = ANY (fullselect)
The values for expression1 and expression2 or the column of fullselect1 in the IN predicate must be compatible. Each expression3 value and its corresponding column of fullselect2 in the IN predicate must be compatible. The Rules for Result Data Types can be used to determine the attributes of the result used in the comparison.
The values for the expressions in the IN predicate (including corresponding columns of a fullselect) can have different code pages. If a conversion is necessary then the code page is determined by applying Rules for String Conversions to the IN list first and then to the predicate using the derived code page for the IN list as the second operand.
Examples:
Example 1: The following evaluates to true if the value in the row under evaluation in the DEPTNO column contains D01, B01, or C01:
DEPTNO IN ('D01', 'B01', 'C01')
Example 2: The following evaluates to true only if the EMPNO (employee number) on the left side matches the EMPNO of an employee in department E11:
EMPNO IN (SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
Example 3: Given the following information, this example
evaluates to true if the specific value in the row of the COL_1 column
matches any of the values in the list:
Table 11. IN Predicate example
| Expressions | Type | Code Page |
|---|---|---|
| COL_1 | column | 850 |
| HV_2 | host variable | 437 |
| HV_3 | host variable | 437 |
| CON_1 | constant | 850 |
When evaluating the predicate:
COL_1 IN (:HV_2, :HV_3, CON_4)
The two host variables will be converted to code page 850 based on the Rules for String Conversions.
Example 4: The following evaluates to true if the specified year in EMENDATE (the date an employee activity on a project ended) matches any of the values specified in the list (the current year or the two previous years):
YEAR(EMENDATE) IN (YEAR(CURRENT DATE),
YEAR(CURRENT DATE - 1 YEAR),
YEAR(CURRENT DATE - 2 YEARS))
Example 5: The following evaluates to true if both ID and DEPT on the left side match MANAGER and DEPTNUMB respectively for any row of the ORG table.
(ID, DEPT) IN (SELECT MANAGER, DEPTNUMB FROM ORG)
(32) The following forms of the comparison operators are also supported in basic and quantified predicates; ^=, ^<, ^>, !=, !< and !>. In addition, in code pages 437, 819, and 850, the forms ¬=, ¬<, and ¬> are supported.
All these product-specific forms of the comparison operators are intended only to support existing SQL that uses these operators, and are not recommended for use when writing new SQL statements.