Basic predicate

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.

If the comparison operator is <, <=, >, or >=, arow-value-expression must not reference a non-deterministic expression, a function with external action, or a scalar fullselect.

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,...,Rxn) <> (Ry1,Ry2,...,Ryn) is:
  • True - if, and only if, Rxi=Ryi evaluates to false for some value of i. That is, there is at least one pair of non-null values, Rxi and Ryi, that are not equal to each other.
  • False - if, and only if, Rxi=Ryi evaluates to true for every value of i. That is, Rx1,Rx2,...,Rxn)=(Ry1,Ry2,...,Ryn) is true.
  • Unknown - if the comparison is neither true nor false. That is, Rxi or Ryi is a null value for some value of i, and there is no value of j such that Rxj=Ryj evaluates to false.
• If the operator is <, the result of the predicate is:
  • True - if, and only if, the first N pairs of the corresponding value expressions are equal and the next pair has the left value expression less than the right value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn < Ryn is true for some value of n.
  • False - if, and only if, all pairs of the corresponding value expressions are equal (Rx = Ry is true) or the first N pairs of the corresponding value expressions are equal and the next pair has the right value expression less than the left value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn >= Ryn is true for some value of n.
  • Unknown - if the comparison is neither true nor false.
  
• If the operator is <=, the result of the predicate is:
  • True - if, and only if, the first N pairs of the corresponding value expressions are equal and the next pair has the left value expression less than or equal to the right value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn <= Ryn is true for some value of n.
  • False - if, and only if, all pairs of the corresponding value expressions are equal (Rx = Ry is true) or the first N pairs of the corresponding value expressions are equal and the next pair has the right value expression less than the left value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn > Ryn is true for some value of n.
  • Unknown - if the comparison is neither true nor false.
  
• If the operator is >, the result of the predicate is:
  • True - if, and only if, the first N pairs of the corresponding value expressions are equal and the next pair has the left value expression greater than the right value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn > Ryn is true for some value of n.
  • False - if, and only if, all pairs of the corresponding value expressions are equal (Rx = Ry is true) or the first N pairs of the corresponding value expressions are equal and the next pair has the right value expression greater than the left value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn <= Ryn is true for some value of n.
  • Unknown - if the comparison is neither true nor false.
  
• If the operator is >=, the result of the predicate is:
  • True - if, and only if, the first N pairs of the corresponding value expressions are equal and the next pair has the left value expression greater than or equal to the right value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn >= Ryn is true for some value of n.
  • False - if, and only if, all pairs of the corresponding value expressions are equal (Rx = Ry is true) or the first N pairs of the corresponding value expressions are equal and the next pair has the right value expression greater than the left value expression for some value of N. That is, Rxi = Ryi is true for all values of i < n and Rxn < Ryn is true for some value of n.
  • Unknown - if the comparison is neither true nor false.
  

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

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