# Basic predicate

A basic predicate compares two values or compares a set of values with another set of values.

>>-+-expression-+-=------+-expression----------------------+--->< | | (1) | | | +-<>-----+ | | +-<------+ | | +->------+ | | +-<=-----+ | | '->=-----' | | (1) | '-row-value-expression--+-=-----+--row-value-expression-' '-<>----'

- Other comparison operators are also supported.
^{1}

When expression is a fullselect, the fullselect must return a single result column with a single value, whether null or not null. If the value of either operand is null or the result of the fullselect is empty, 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, another row-value-expression, with an identical number of value expressions, must be specified on the right side. The data types of the corresponding expressions or columns 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. The result of the predicate depends on the operator, as in the following:

- If the operator is =, the result of the predicate is:
- True - if all pairs of corresponding value expressions evaluate to true.
- False - if any one pair of corresponding value expressions evaluates to false.
- Unknown - if the comparisons is neither true nor false. That is, if at least one comparison of corresponding value expressions is unknown because of a null value and no pair of corresponding value expressions evaluates to false.

- If the operator is <>, the result of the predicate (Rx1,Rx2,...,R
`xn`) <> (Ry1,Ry2,...,R`yn`) is:- True - if, and only if, R
`xi`=R`yi`evaluates to false for some value of`i`. That is, there is at least one pair of non-null values, R`xi`and R`yi`, that are not equal to each other. - False - if, and only if, R
`xi`=R`yi`evaluates to true for every value of`i`. That is, Rx1,Rx2,...,R`xn`)=(Ry1,Ry2,...,R`yn`) is true. - Unknown - if the comparison is neither true nor false. That is, R
`xi`or R`yi`is a null value for some value of`i`, and there is no value of`j`such that R`xj`=R`yj`evaluates to false.

- True - if, and only if, R

Predicate | Is true if and only if ... |
---|---|

x = y |
x is equal to y |

x <> y |
x is not equal to y |

x < y |
x is less than y |

x > y |
x is greater than y |

x <= y |
x is less than or equal to y |

x >= y |
x is greater than or equal
to y |

*Examples for values x and y:*

```
EMPNO = '528671'
SALARY < 20000
PRSTAFF <> :VAR1
SALARY >= (SELECT AVG(SALARY) FROM DSN8A10.EMP)
```

*Example:*List the name, first name, and salary of the employee who is responsible for the 'SECRET' project. This employee might appear in either the PROJA1 or PROJA2 tables. A UNION is used in case the employee appears in both tables to eliminate duplicate RESPEMP values.

```
SELECT LASTNAME, FIRSTNAME, SALARY
FROM DSN8A10.EMP X
WHERE EMPNO = (
SELECT RESPEMP
FROM PROJA1 Y
WHERE MAJPROJ = 'SECRET'
UNION
SELECT RESPEMP
FROM PROJA2 Z
WHERE MAJPROJ = 'SECRET');
```

^{1}The following forms of the comparison operators are also supported in basic and quantified predicates in code pages where the exclamation point is X'5A': !=, !<, and !> . In addition, the forms ¬=, ¬<, and ¬> are supported as long as the code point used for the logical not symbol is the correct one for the specified code page. These forms of the operators are intended only to support existing SQL statements that use them and are not recommended for use when writing new SQL statements.

A logical not sign (¬) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target CCSIDs. To avoid this problem, substitute an equivalent operator for any operator that includes a not sign. For example, substitute '<>' for '¬=', '<=' for '¬>', and '>=' for '¬<'.