A predicate specifies a condition that is true, false, or unknown about a given row or group.
The following rules apply to all types of predicates:
Row-value expression: The operand of several
predicates (basic, quantified, and IN) can be a row-value-expression:
A row-value-expression returns a single row that consists of one or more column values. The values can be specified as a list of expressions. The number of columns that are returned by the row-value-expression is equal to the number of expressions that are specified in the list.
A basic predicate compares two values or compares a set of values with another set of values.
When a single expression is specified on the left side of the operator, another expression must be specified on the right side. The data types of the corresponding expressions must be compatible. The value of the expression on the left side is compared with the value of the expression on the right side. If the value of either operand is null, the result of the predicate is unknown. Otherwise the result is either true or false.
When a row-value-expression is specified on the left side of the operator (= or <>) and another row-value-expression is specified on the right side of the operator, both row-value-expressions must have the same number of value expressions. The data types of the corresponding expressions of the row-value-expressions must be compatible. The value of each expression on the left side is compared with the value of its corresponding expression on the right side.
When a row-value-expression is specified and a fullselect is also specified:
The result of the predicate depends on the operator:
If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence.
For values x and y:
Example 1
EMPNO = '528671' PRTSTAFF <> :VAR1 SALARY + BONUS + COMM < 20000 SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
Example 2: List the name, first name, and salary of the employee who is responsible for the 'OP1000' project.
SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE X WHERE EMPNO = ( SELECT RESPEMP FROM PROJA1 Y WHERE MAJPROJ = 'OP1000' )
A quantified predicate compares a value or values with a set of values.
When expression is specified, the fullselect must return a single result column. The fullselect can return any number of values, whether null or not null. The result depends on the operator that is specified:
When row-value-expression is specified, the number of result columns returned by the fullselect must be the same as the number of value expressions specified by row-value-expression. The fullselect can return any number of rows of values. The data types of the corresponding expressions of the row value expressions must be compatible. The value of each expression from row-value-expression is compared with the value of the corresponding result column from the fullselect. SELECT * is not allowed in the outermost select lists of the fullselect.
The value of the predicate depends on the operator that is specified:
If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence.
Table TBLA
COLA
-----
1
2
3
4
nullTable TBLB
COLB
-----
2
3
Example 1
SELECT * FROM TBLA WHERE COLA = ANY(SELECT COLB FROM TBLB)
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 * FROM TBLA WHERE COLA > ANY(SELECT COLB FROM TBLB)
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 * FROM TBLA WHERE COLA > ALL(SELECT COLB FROM TBLB)
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 * FROM TBLA WHERE COLA > ALL(SELECT COLB FROM TBLB WHERE COLB<0)
Results in 1,2,3,4, and null. The subselect returns no values. Thus, the predicate is true for all rows in TBLA.
Example 5
SELECT * FROM TBLA WHERE COLA > ANY(SELECT COLB FROM TBLB WHERE COLB<0)
Results in the empty set. The subselect returns no values. Thus, the predicate is false for all rows in TBLA.
The BETWEEN predicate compares a value with a range of values.
If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence.
The BETWEEN predicate:
value1 BETWEEN value2 AND value3
is logically 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.
If the operands of the BETWEEN predicate are strings with different CCSIDs, operands are converted as if the above logically-equivalent search conditions were specified.
Given a mixture of datetime values and string representations of datetime values, all values are converted to the data type of the datetime operand.
EMPLOYEE.SALARY BETWEEN 20000 AND 40000 SALARY NOT BETWEEN 20000 + :HV1 AND 40000
The DISTINCT predicate compares a value with another value.
When the predicate is IS DISTINCT, the result of the predicate is true if the comparison of the expressions evaluates to true. Otherwise, the result of the predicate is false. The result cannot be unknown.
When the predicate IS NOT DISTINCT FROM, the result of the predicate is true if the comparison of the expressions evaluates to true (null values are considered equal to null values). Otherwise, the predicate is false. The result cannot be unknown.
The DISTINCT predicate:
value1 IS NOT DISTINCT FROM value2 is logically equivalent to the search condition:
( value1 IS NOT NULL AND value2 IS NOT NULL AND value1 = value2 ) OR ( value1 IS NULL AND value2 IS NULL )
The DISTINCT predicate:
value1 IS DISTINCT FROM value2 is logically equivalent to the search condition:
NOT (value1 IS NOT DISTINCT FROM value2)
If the operands of the DISTINCT predicate are strings with different CCSIDs, operands are converted as if the above logically-equivalent search conditions were specified.
Assume that table T1 exists and it has a single column C1, and three rows with the following values for C1: 1, 2, null. The following query produces the following results:
SELECT * FROM T1 WHERE C1 IS DISTINCT FROM :HV
| C1 | :HV | Result |
|---|---|---|
| 1 | 2 | True |
| 2 | 2 | False |
| 1 | Null | True |
| Null | Null | False |
The following query produces the following results:
SELECT * FROM T1 WHERE C1 IS NOT DISTINCT FROM :HV
| C1 | :HV | Result |
|---|---|---|
| 1 | 2 | False |
| 2 | 2 | True |
| 1 | Null | False |
| Null | Null | True |
The EXISTS predicate tests for the existence of certain rows. The fullselect may specify any number of columns, and
The values returned by the fullselect are ignored.
EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY > 60000)
The IN predicate compares a value or values with a set of values.
When a single expression is specified on the left side of the operator, the IN predicate compares a value with a set of values. When a fullselect is specified, the fullselect must return a single result column, and can return any number of values, whether null or not null. The data type of expression and the data type of the result column of the fullselect or the expression on the right side of the operator must be compatible. Each variable must identify a structure or variable that is described in accordance with the rule for declaring host structures or variables.
When a row-value-expression is specified, the IN predicate compares values with a collection of values.
The value of the predicate depends on the operator that is specified:
If the corresponding operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence.
An IN predicate is equivalent to other predicates as follows:
| IN predicate | Equivalent predicate |
|---|---|
| expression IN (expression) | expression = expression |
| expression IN (fullselect) | expression = ANY (fullselect) |
| expression NOT IN (fullselect) | expression <> ALL (fullselect) |
| expression IN (value1, value2, ..., valuen) | expression IN (SELECT * FROM R)
Where T is a table with a single
row and R is a temporary table formed by the following fullselect:
SELECT value1 FROM T UNION SELECT value2 FROM T UNION . . . UNION SELECT valuen FROM T |
| row-value-expression IN (fullselect) | row-value-expression = SOME ( fullselect) |
| row-value-expression IN (fullselect) | row-value-expression = ANY ( fullselect) |
| row-value-expression NOT IN (fullselect) | row-value-expression <> ALL ( fullselect) |
If the operands of the IN predicate have different data types or attributes, the rules used to determine the data type for evaluation of the IN predicate are those for UNION, UNION ALL, EXCEPT, and INTERSECT. For a description, see Rules for result data types.
If the operands of the IN predicate are strings with different CCSIDs, the rules used to determine which operands are converted are those for operations that combine strings. For a description, see Conversion rules for operations that combine strings.
DEPTNO IN ('D01', 'B01', 'C01') EMPNO IN(SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which the underscore and percent sign have special meanings. Trailing blanks in a pattern are a part of the pattern.
If the value of any of the arguments is null, the result of the LIKE predicate is unknown.
The match-expression, pattern-expression, and escape-expression must identify strings or numbers. A numeric argument is cast to a character string before evaluating the predicate. For more information on converting numeric to a character string, see VARCHAR. The values for match-expression, pattern-expression, and escape-expression must either all be binary strings or none can be binary strings. The three arguments can include a mixture of character strings and graphic strings.
None of the expressions can yield a distinct type. However, it can be a function that casts a distinct type to its source type.
If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence. An ICU sort sequence is not allowed with a LIKE predicate.
With character strings, the terms character, percent sign, and underscore in the following discussion refer to single-byte characters. With graphic strings, the terms refer to double-byte or Unicode characters. With binary strings, the terms refer to the code points of those single-byte characters.
Simple description: A simple description of the LIKE pattern is as follows:
If the pattern-expression needs to include either the underscore or the percent character, the escape-expression is used to specify a character to precede either the underscore or percent character in the pattern.
Rigorous description: Let x denote a value of match-expression and y denote the value of pattern-expression.
The string y is interpreted as a sequence of the minimum number of substring specifiers so each character of y is part of exactly one substring specifier. A substring specifier is an underscore, a percent sign, or any nonempty sequence of characters other than an underscore or a percent sign.
The result of the predicate is unknown if x or y is the null value. Otherwise, the result is either true or false. The result is true if x and y are both empty strings or if there exists a partitioning of x into substrings such that:
It follows that if y is an empty string and x is not an empty string, the result is false. Similarly, it follows that if y is an empty string and x is not an empty string consisting of other than percent signs, the result is false.
The predicate x NOT LIKE y is equivalent to the search condition NOT(x LIKE y).
If necessary, the CCSID of the match-expression, pattern-expression, and escape-expression are converted to the compatible CCSID between the match-expression and pattern-expression.
Mixed data: If the column is mixed data, the pattern can include both SBCS and DBCS characters. The special characters in the pattern are interpreted as follows:
Unicode data: For Unicode, the special characters in the pattern are interpreted as follows:
When the LIKE predicate is used with Unicode data, the Unicode percent sign and underscore use the code points indicated in the following table:
| Character | UTF-8 | UTF-16 or UCS-2 |
|---|---|---|
| Half-width % | X'25' | X'0025' |
| Full-width % | X'EFBC85' | X'FF05' |
| Half-width _ | X'5F' | X'005F' |
| Full-width _ | X'EFBCBF' | X'FF3F' |
The full-width or half-width % matches zero or more characters. The full-width or half width _ character matches exactly one character. (For EBCDIC data, a full-width _ character matches one DBCS character.)
Parameter marker:
When the pattern specified in a LIKE predicate is a parameter marker, and a fixed-length character variable is used to replace the parameter marker; specify a value for the variable that is the correct length. If a correct length is not specified, the select will not return the intended results.
For example, if the variable is defined as CHAR(10), and the value WYSE% is assigned to that variable, the variable is padded with blanks on assignment. The pattern used is
'WYSE% '
This pattern requests the database manager to search for all values that start with WYSE and end with five blank spaces. If you intended to search for only the values that start with 'WYSE' you should assign the value 'WYSE%%%%%%' to the variable.
For example, if '+' is the escape character, any occurrences of '+' other than '++', '+_', or '+%' in the pattern-expression is an error.
The following example shows the effect of successive occurrences of the escape character, which in this case is the plus sign (+).
| When the pattern string is... | The actual pattern is... |
|---|---|
| +% | A percent sign |
| ++% | A plus sign followed by zero or more arbitrary characters |
| +++% | A plus sign followed by a percent sign |
Search for the string 'SYSTEMS' appearing anywhere within the PROJNAME column in the PROJECT table.
SELECT PROJNAME FROM PROJECT WHERE PROJECT.PROJNAME LIKE '%SYSTEMS%'
Search for a string with a first character of 'J' that is exactly two characters long in the FIRSTNME column of the EMPLOYEE table.
SELECT FIRSTNME FROM EMPLOYEE WHERE EMPLOYEE.FIRSTNME LIKE 'J_'
In this example:
SELECT * FROM TABLEY WHERE C1 LIKE 'AAAA+%BBB%' ESCAPE '+'
'+' is the escape character and indicates that the search is for a string that starts with 'AAAA%BBB'. The '+%' is interpreted as a single occurrence of '%' in the pattern.
Assume that a distinct type named ZIP_TYPE with a source data type of CHAR(5) exists and an ADDRZIP column with data type ZIP_TYPE exists in some table TABLEY. The following statement selects the row if the zip code (ADDRZIP) begins with '9555'.
SELECT * FROM TABLEY WHERE CHAR(ADDRZIP) LIKE '9555%'
The RESUME column in sample table EMP_RESUME is defined as a CLOB. If the variable LASTNAME has a value of 'JONES', the following statement selects the RESUME column when the string JONES appears anywhere in the column.
SELECT RESUME FROM EMP_RESUME WHERE RESUME LIKE '%'||LASTNAME||'%'
In the following table of EBCDIC examples, assume COL1 is mixed data. The table shows the results when the predicates in the first column are evaluated using the COL1 values from the second column:
The NULL predicate tests for null values.
The result of a NULL predicate cannot be unknown. If the value of the expression is null, the result is true. If the value is not null, the result is false.
If NOT is specified, the result is reversed.
EMPLOYEE.PHONE IS NULL SALARY IS NOT NULL